Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
I have the following Sumproduct formula and I'm getting a #Value error
=SUMPRODUCT(--(ISNUMBER(32:32)),--(YEAR(32:32)=U16)) I know that A32 is not a number, so the YEAR(32:32) part returns #VALUE for the first entry. How do I get around this? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
I don't know what you are trying to accomplish?
32:32 is not the number "Barb Reinhardt" wrote: I have the following Sumproduct formula and I'm getting a #Value error =SUMPRODUCT(--(ISNUMBER(32:32)),--(YEAR(32:32)=U16)) I know that A32 is not a number, so the YEAR(32:32) part returns #VALUE for the first entry. How do I get around this? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
I'm looking at the array of row 32 to see if the year of the values matches
another predefined year. It's not working when I get the #VALUE error on the first cell. Apparently anything * #VALUE = #VALUE. Can you help? "Teethless mama" wrote: I don't know what you are trying to accomplish? 32:32 is not the number "Barb Reinhardt" wrote: I have the following Sumproduct formula and I'm getting a #Value error =SUMPRODUCT(--(ISNUMBER(32:32)),--(YEAR(32:32)=U16)) I know that A32 is not a number, so the YEAR(32:32) part returns #VALUE for the first entry. How do I get around this? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct question
Teethless mama wrote...
I don't know what you are trying to accomplish? 32:32 is not the number It's a range, equivalent to A32:IV32, i.e., the entire 32nd row of the active worksheet. "Barb Reinhardt" wrote: I have the following Sumproduct formula and I'm getting a #Value error =SUMPRODUCT(--(ISNUMBER(32:32)),--(YEAR(32:32)=U16)) I know that A32 is not a number, so the YEAR(32:32) part returns #VALUE for the first entry. How do I get around this? You can't use SUMPRODUCT because you need to mask errors. Usually that means you'd need to use IF to do so, but in this particular case you could just use COUNT. However, either way that means you need to use an array formula. Try the array formulas =COUNT(1/(YEAR(32:32)=U16)) or =SUM(IF(ISNUMBER(YEAR(32:32)),--(YEAR(32:32)=U16))) Now if only A2 is nonblank and nonnumeric, you could try pruning it from your range. =SUMPRODUCT(--(YEAR(B32:IV32)=U16)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct question | Excel Discussion (Misc queries) | |||
A SUMPRODUCT question | Excel Discussion (Misc queries) | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) |