URGENT!!! SUM(IF CSE FOR MULTIPLE CONDITION
Posted by MILAN on December 06, 2001 6:00 AM
I WANT TO COUNT THE OCCURANCE OF THE NUMBER "1" IN
COLUMN E WHEN THERE IS THE OCCURANCE OF "396A" IN
COLUME C. I WANT TO SEARCH ROWS 2 THROUGH 315 WHICH
COULD INCLUDE BLANK CELLS IN ROWS C AND E.
I HAVE TRIED ALL THE SAMPLE CSE FORMULAS I COULD FIND
ON THE MESSAGE BOARD WITHOUT SUCCESS.
Posted by Juan Pablo G. on December 06, 2001 6:44 AM
Please, DON'T POST ALL IN CAPS !, that's considered shouting, therefor, rude.
Have you tried
=SUMPRODUCT((C2:C315="396A")*(E2:E315=1))
This IS NOT an array formula.
Juan Pablo G.
Posted by Aladin Akyurek on December 06, 2001 6:46 AM
Multiconditional Count
Milan --
=SUMPRODUCT((ISNUMBER(E2:E315)*(E2:E315=1))*(C2:C315="396A"))
Aladin
Posted by Milan on December 06, 2001 7:14 AM
Re: Multiconditional Count
Aladin...
Thanks for the timely response. The above formula worked except the total shows 1 less than the actual count.
I tried it with the number "2" in colume E and the resulting total shows 1 less than the actual count.
should the cell address includ the header cell?
Posted by Aladin Akyurek on December 06, 2001 7:18 AM
Re: Multiconditional Count
No. Try
=SUMPRODUCT((ISNUMBER(E2:E315)*(E2:E315=1))*(TRIM(C2:C315)="396A"))
in case you have a 396A value with spaces arournd.
Posted by Milan on December 06, 2001 7:28 AM
Re: Multiconditional Count
Aladin...
Some how It now works without the trim option.
Again Thanks. This is greatly appreciated.
Posted by Milan on December 06, 2001 9:38 AM
Re: Multiconditional Count
This works great: =SUMPRODUCT((ISNUMBER($E$2:$E$315)*($E$2:$E$315=1))*($C$2:$C$315="396A"))
This does not,results in a o. Why? =SUMPRODUCT((ISNUMBER($H$2:$H$315)*($H$2:$H$315="LI"))*($C$2:$C$315="396K"))
I changed the column designation from E to H and the number 1 to "LI".
Posted by Juan Pablo G. on December 06, 2001 9:42 AM
Re: Multiconditional Count
Because "LI" is not a number. Erase this
ISNUMBER($H$2:$H$315)
Juan Pablo G.
Posted by MILAN on December 06, 2001 11:33 AM
Re: (SOLVED)Multiconditional Count
YOUR ORIGINAL FORMULA YOU OFFERED WORKS GREAT. THANK
Posted by Aladin Akyurek on December 06, 2001 12:01 PM
Just for the record...
[1, Juan] =SUMPRODUCT(($E$2:$E$315=1)*($C$2:$C$315="396A"))
[2, Aladin] =SUMPRODUCT((ISNUMBER($E$2:$E$315)*($E$2:$E$315=1))*($C$2:$C$315="396A"))
Both formulas are correct and equivalent as long as $E$2:$E$315 is numeric (interspersed with blank cells) and you don't apply less than operator to the numeric column.
[2] contains a ISNUMBER test in case you might want to compute with less than operator like in
=SUMPRODUCT((ISNUMBER($E$2:$E$315)*($E$2:$E$315 < 3))*($C$2:$C$315="396A"))
The first formula will produce wrong count when there is a blank and a corresponding 396A.
Regards,
Aladin
PS. If "LI" is text, the formula without ISNUMBER test, as Juan suggested, should be used.
======
Posted by J. Caesar on December 06, 2001 2:54 PM
What do mean LI is not a number? (NT)
Posted by Juan Pablo G. on December 07, 2001 6:38 AM
Re: What do mean LI is not a number? (NT)
In the formula
((ISNUMBER($H$2:$H$315)*($H$2:$H$315="LI"))*
He's checking for STRING "LI", there for if he checks for NUMBERS (ISNUMBER($H$2:$H$315) AND when it finds a number if it's equal to "LI" (A string) return TRUE. This can NEVER Happen !! take this column for example.
{1;2;3;"LI";5;8;"LI";"LI"}
If you do this in another formula
=ISNUMBER(A1:A8)+0
and press F9 while in the formula bar, you should get this:
{1;1;1;0;1;1;0;0}
Now, in another formula put
=(A1:A8="LI")+0
you'll get
{0;0;0;1;0;0;1;1}
If you multiplty them (ISNUMBER * ="LI"), you'll get
{0;0;0;0;0;0;0;0}
That's why that formula can't work.
Juan Pablo G.
Posted by Julias Ceaser on December 07, 2001 8:36 AM
Sorry Just kidding - LI = 51 to me
