Count with an AND statement

[alpad]

I have a spreadsheet that has two columns: one has text that can have multiple comma separated values (e.g. Tomato, Carrot), the other has a number.

I would like to create a function that gives me a count of:

a. a word appearing in column a, AND
b. a particular number appearing in column b

I have tried the COUNTA and the COUNTIF functions with no luck.

Thanks!
Alpa

[wjohnson]

In your example in Cell A1 = Red, Cell B1 = 2, In Cell C1 I would enter a formula that gives you a True or False Answer (=AND(A1="black",B1=1)) and then copy it down. Then do a countif on column C for "True" or "False"
If you just use an "and" function - then the result is TRUE or FALSE only
If you use and IF-AND then the value in C can be any text you want.

[alpad]

Thank you,

I used the following function, but it appears to be invalid:

=AND('Product Requirements'!L5:L102 = 'Infrastructure',,'Product Requirements'!P5:P102 = 1)

It appears as if I will not be able to use mulitple rows? Should I check a cell at a time and then add up the results?

Thanks,
Alpa

[wjohnson]

No - In cell C1 setup your "AND" Statement to be true or false depending on the conditions you want for Cells A1 and B1. Then you are going copy the formula down as far as you need to go. Now each cell in Row C should be True or False. Now in cell D1 you can use the countif statement for Column C:C for a True or False Condition, depending on what you want to count.
Hope this helps

[starguy]

I have a spreadsheet that has two columns: one has text that can have multiple comma separated values (e.g. Tomato, Carrot), the other has a number.

I would like to create a function that gives me a count of:

a. a word appearing in column a, AND
b. a particular number appearing in column b

I have tried the COUNTA and the COUNTIF functions with no luck.

Thanks!
Alpa

try this

=SUM(IF(Product Requirements!L5:L102="Infrastructure",IF(Product Requirements!P5:P102=1,1,0),0)

hit Ctrl+Shift+Enter (only pressing Enter will not work as it is an array formula)

this will return number of cells which contain word "Infrastructure" in range L5:L102 and has value 1 in corresponding cell in range P5:P102.

Regards.

[robotmannick]

It sounds like an ideal place to use a "Sumproduct" formula. Once you start using these, you'll wonder how you ever did without them before.

Try:

=SUMPRODUCT(('Product Requirement'!L5:L102="infrastructure")*('Product Requirement'!P5:P102=1))

Any use?

[robert111]

if cell A1 contains red, black, blue then in cell B1 put

=search("black",a1)

this will return a positive integer if it finds the required colour or an error message if not. Use IF(iserror........to trap the error message

finally build it into an overall IF statement

hope this helps, and by the way I endorse the statement above on the sumproduct function.....

[Dav]

if your data is in a1:b7 and you wish to count red in column A and 1 in column B enter the following as an array (holding down shift and control then pressing enter)

=SUM(IF(ISERROR(SEARCH("red",A1:A7)),0,IF(B1:B7=1,1,0)))

just a warning that if red appears in another word it will count it as well eg spred (not that it is spelt correctly!), redundant, sparred etc

Regards

Dav

[alpad]

Thank you to all that replied. I used the SUMPRODUCT function and it worked like a charm.


=SUMPRODUCT(('Product Requirements'!L5:L102="Infrastructure")*('Product Requirements'!P5:P102=1))


A quick follow up question: If each cell in column L can have multiple values (such as EX, Infrastructure, Client); how do I modify the above to count this as well? The formula works great when the cell has the word Infrastructure in it by itself.

Thanks!

Alpa

[Dav]

The array formula copes with multiple entries in the cell.

enter the following as an array (holding down shift and control then pressing enter)

=SUM(IF(ISERROR(SEARCH("Infrastructure",'Product Requirements'!L5:L102)),0,IF('Product Requirements'!P5:P102=1, 1,0)))

just a warning that if red appears in another word it will count it as well eg spred (not that it is spelt correctly!), redundant, sparred etc

Regards

Dav