help with formula

[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

 

[Biff]

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

 

[Guest]

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

 

[Biff]

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

 

[Guest]

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