Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |