ExcelBanter - help with formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - help with formula (https://www.excelbanter.com/excel-worksheet-functions/50840-help-formula.html)

help with formula

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

help with formula

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

"Alex" wrote in message
...
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

help with formula

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" wrote:

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

"Alex" wrote in message
...
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

help with 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

"Alex" wrote in message
...
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" wrote:

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

"Alex" wrote in message
...
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

help with formula

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

"Biff" wrote:

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

"Alex" wrote in message
...
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" wrote:

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

"Alex" wrote in message
...
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