# Thread: Counting Value with 2 criteria (2003)

1. ## Counting Value with 2 criteria (2003)

Loungers I need to be able to count the number of occurrence that meet 2 criteria. Column A contains text and column B numbers. I want to be able to count the occurrence where say column A contains the word "test" and column B contains the number 1 (for example) any ideas?

2. ## Re: Counting Value with 2 criteria (2003)

Dean,

Use and array formula like (Assumes the list is in a2:b2000 - edit as appropriate)

=sum((a2:a2000='test')*(b2:b2000=1))

Remember that the array formula is entered with ctrl-shift-enter not just enter. If you do it correctly Excel will add { } around the formula.

3. ## Re: Counting Value with 2 criteria (2003)

Thanks for the quick rely kieran - will give it a shot

4. ## Re: Counting Value with 2 criteria (2003)

If column A contains only the word "test"

Two other options are :

=COUNT(IF(A1:A100="test",IF(B1:B100=1,1)))

(Array formula confirmed with ctrl-shift-enter )

or

=SUMPRODUCT(--(A1:A100="test"),--(B1:B100=1))

If column A contains the word " testA, testB, testC..."

Then, the formula will be :

=SUMPRODUCT(--(SEARCH("test",A1:A100)),--(B1:B100=1))

Regards

5. ## Re: Counting Value with 2 criteria (2003)

Thanks bosco_yip will try this also

Regards

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•