Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumproduct Function problem
Hi, guys,
I have a Range A1:A10 with the following: A1 - 01-Jan A2 - 02 -Jan A3 - 01-Feb A4 - 02-Feb A5 (Blank) A6(Blank) A7(Blank) A8 - 03-Jan A9(Blank) A10(Blank) If I want to count how many cells have "Jan" with the formula: =SUMPRODUCT(--(TEXT($A$1:$A$5;"mmm")="Jan")) it gives me the number 8. However, really the number is 3. But, if the above formula is with "Feb", the number is correct, i.e. - 2. I understood that for unknown reason to me, the first formula counts the blank cells as they are with "Jan" too. So, if I write: =SUMPRODUCT(--(TEXT($A$1:$A$10;"mmm")="Jan"))-SUMPRODUCT(--ISBLANK(A1:A10)) the result is now correct, I mean, number 3. Any ideas for this? Thanks in advance. -- JRod |
#2
|
|||
|
|||
Here is an alternative.
=SUMPRODUCT((MONTH(A1:A5)=1)*(ISNUMBER(A1:A5))) This at least uses one fewer function call than your alternative. But yes you're right, if you don't check for the content of the range, Excel will for some reason consider blank cells to be equal to 0 in the MONTH(A1:A5) part, which is a January date a long time ago. "JRod" wrote in message ... Hi, guys, I have a Range A1:A10 with the following: A1 - 01-Jan A2 - 02 -Jan A3 - 01-Feb A4 - 02-Feb A5 (Blank) A6(Blank) A7(Blank) A8 - 03-Jan A9(Blank) A10(Blank) If I want to count how many cells have "Jan" with the formula: =SUMPRODUCT(--(TEXT($A$1:$A$5;"mmm")="Jan")) it gives me the number 8. However, really the number is 3. But, if the above formula is with "Feb", the number is correct, i.e. - 2. I understood that for unknown reason to me, the first formula counts the blank cells as they are with "Jan" too. So, if I write: =SUMPRODUCT(--(TEXT($A$1:$A$10;"mmm")="Jan"))-SUMPRODUCT(--ISBLANK(A1:A10)) the result is now correct, I mean, number 3. Any ideas for this? Thanks in advance. -- JRod |
#3
|
|||
|
|||
Thanks, Dave
I really didn't know that 0 was a January date a long time ago. -- JRod "Dave R." escreveu na mensagem ... Here is an alternative. =SUMPRODUCT((MONTH(A1:A5)=1)*(ISNUMBER(A1:A5))) This at least uses one fewer function call than your alternative. But yes you're right, if you don't check for the content of the range, Excel will for some reason consider blank cells to be equal to 0 in the MONTH(A1:A5) part, which is a January date a long time ago. "JRod" wrote in message ... Hi, guys, I have a Range A1:A10 with the following: A1 - 01-Jan A2 - 02 -Jan A3 - 01-Feb A4 - 02-Feb A5 (Blank) A6(Blank) A7(Blank) A8 - 03-Jan A9(Blank) A10(Blank) If I want to count how many cells have "Jan" with the formula: =SUMPRODUCT(--(TEXT($A$1:$A$5;"mmm")="Jan")) it gives me the number 8. However, really the number is 3. But, if the above formula is with "Feb", the number is correct, i.e. - 2. I understood that for unknown reason to me, the first formula counts the blank cells as they are with "Jan" too. So, if I write: =SUMPRODUCT(--(TEXT($A$1:$A$10;"mmm")="Jan"))-SUMPRODUCT(--ISBLANK(A1:A10)) the result is now correct, I mean, number 3. Any ideas for this? Thanks in advance. -- JRod |
#4
|
|||
|
|||
An empty cell is read off as 0, a result that gets mapped to a January date.
Assuming that you have true dates in A1:A10 and you're interested to count, say, Jan 2005 dates. In C1 enter the first of the month/year of interest as a true date: 1-Jan-05 In C2 enter: =SUMPRODUCT(--(DATE(YEAR(A1:A10),MONTH(A1:A10),1)=C1)) The result should be 3. JRod wrote: Hi, guys, I have a Range A1:A10 with the following: A1 - 01-Jan A2 - 02 -Jan A3 - 01-Feb A4 - 02-Feb A5 (Blank) A6(Blank) A7(Blank) A8 - 03-Jan A9(Blank) A10(Blank) If I want to count how many cells have "Jan" with the formula: =SUMPRODUCT(--(TEXT($A$1:$A$5;"mmm")="Jan")) it gives me the number 8. However, really the number is 3. But, if the above formula is with "Feb", the number is correct, i.e. - 2. I understood that for unknown reason to me, the first formula counts the blank cells as they are with "Jan" too. So, if I write: =SUMPRODUCT(--(TEXT($A$1:$A$10;"mmm")="Jan"))-SUMPRODUCT(--ISBLANK(A1:A10)) the result is now correct, I mean, number 3. Any ideas for this? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Function Problem | Excel Worksheet Functions | |||
Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
SUMPRODUCT Problem | Excel Discussion (Misc queries) | |||
Excel Send To function problem | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions |