help with formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to count the number of items in a column if some conditions are met in
other columns:

Col1 Col2 Col3
Item1 Yes Sep
Item1 No Aug
Item1 Yes Sep
Item1 N/A Sep
Item2 No Aug
Item3 Yes Nov
....
For this spreadsheet I need to calculate how many "Yes" for the items from
the first column for Sep, e.g..

I'm trying to use the array formular:
{=COUNTA($A$4:$A$1000=A2)*($C$4:$C$1000="Sep")*($B$4:$B$1000="Yes")}
But,it's not working.

Could anybody advise anything?

Thanks
 
Hi!

Try this:

=SUMPRODUCT(--(A1:A100="item1"),--(B1:B100="yes"),--(C1:C100="Sep"))

Better to use clls to hold the criteria:

D1 = item1
E1 = yes
F1 = Sep

Then:

=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1),--(C1:C100=F1))

Biff
 
Thanks a lot, Biff.
It's working
But, somehow having 4 Yes for Sep I have only 3 as a result from this formula.
 
Try this:

=SUMPRODUCT(--(TRIM(A1:A100)="item1"),--(TRIM(B1:B100)="yes"),--(TRIM(C1:C100)="Sep"))

If that returns the correct count then that means you have some extraneous
spaces in one (or more) of the ranges.

Biff
 
Thank you very much, Biff.
It's working perfectly even without trim. I confused columns. This is why it
wasn't working.
 
Back
Top