Sumproduct formula
in [General]
|
From: Nate on 27 Apr 2010 14:41 I have a sumproduct formula that was working fine. In my table that it looks up to 1 of the columns contains a formula that returns Q1, Q2, etc... based on the date of another column in the table. 1 of the criteria in my sumproduct formula filters data based on the Q1, Q2 column of my table. I changed the formula that returns Q1, Q2, etc... so that it returns the same values based on a date in a different column. For some reason once I do that my sumproduct formula stops working (returns a #VALUE error)? Any suggestions would be greatly appreciated. Thanks, Nate
From: JLatham on 27 Apr 2010 14:49 Most likely problem is that one of the columns (either old or, most likely, new) that you think held dates, didn't actually hold dates, but held text that looks like dates. And since you can't do math with text, #VALUE is returned. "Nate" wrote: > I have a sumproduct formula that was working fine. In my table that it looks > up to 1 of the columns contains a formula that returns Q1, Q2, etc... based > on the date of another column in the table. 1 of the criteria in my > sumproduct formula filters data based on the Q1, Q2 column of my table. I > changed the formula that returns Q1, Q2, etc... so that it returns the same > values based on a date in a different column. For some reason once I do that > my sumproduct formula stops working (returns a #VALUE error)? Any > suggestions would be greatly appreciated. Thanks, > > Nate
From: Nate on 27 Apr 2010 15:03 That was the first thing I tried, but it didn't seem to have any effect. Plus the sumproduct formula didn't have any direct references to the date column itself. It only referenced the column containing Q1, Q2, etc... which was a text based on the date. "JLatham" wrote: > Most likely problem is that one of the columns (either old or, most likely, > new) that you think held dates, didn't actually hold dates, but held text > that looks like dates. And since you can't do math with text, #VALUE is > returned. > > "Nate" wrote: > > > I have a sumproduct formula that was working fine. In my table that it looks > > up to 1 of the columns contains a formula that returns Q1, Q2, etc... based > > on the date of another column in the table. 1 of the criteria in my > > sumproduct formula filters data based on the Q1, Q2 column of my table. I > > changed the formula that returns Q1, Q2, etc... so that it returns the same > > values based on a date in a different column. For some reason once I do that > > my sumproduct formula stops working (returns a #VALUE error)? Any > > suggestions would be greatly appreciated. Thanks, > > > > Nate
From: Fred Smith on 27 Apr 2010 20:07 Post your formula, and the data you are using. Regards, Fred "Nate" <Nate(a)discussions.microsoft.com> wrote in message news:2F16C105-AF75-43CB-B270-929EA83E4078(a)microsoft.com... > That was the first thing I tried, but it didn't seem to have any effect. > Plus the sumproduct formula didn't have any direct references to the date > column itself. It only referenced the column containing Q1, Q2, etc... > which was a text based on the date. > > "JLatham" wrote: > >> Most likely problem is that one of the columns (either old or, most >> likely, >> new) that you think held dates, didn't actually hold dates, but held text >> that looks like dates. And since you can't do math with text, #VALUE is >> returned. >> >> "Nate" wrote: >> >> > I have a sumproduct formula that was working fine. In my table that it >> > looks >> > up to 1 of the columns contains a formula that returns Q1, Q2, etc... >> > based >> > on the date of another column in the table. 1 of the criteria in my >> > sumproduct formula filters data based on the Q1, Q2 column of my table. >> > I >> > changed the formula that returns Q1, Q2, etc... so that it returns the >> > same >> > values based on a date in a different column. For some reason once I >> > do that >> > my sumproduct formula stops working (returns a #VALUE error)? Any >> > suggestions would be greatly appreciated. Thanks, >> > >> > Nate
|
Pages: 1 Prev: Joining Two formulas with the Help of AND / OR / IF Functions to g Next: vlookup maybe |