Compound countif statement

Thanks:  0
Likes:  0

# Thread: Compound countif statement

1. ## Compound countif statement

Hello, I need some assistance with a countif, or a better formula to use. I want to count all the items in column D that match "Customer" only if the range in column I matches "New England". Any assistance would be appreciated.

COUNTIF(I2:I201, "*"&"New England"&"*"),(D2:D201, "*"&"Customer"&"*"))

2. ## Re: Compound countif statement

Originally Posted by mikeconstantino
Hello, I need some assistance with a countif, or a better formula to use. I want to count all the items in column D that match "Customer" only if the range in column I matches "New England". Any assistance would be appreciated.

COUNTIF(I2:I201, "*"&"New England"&"*"),(D2:D201, "*"&"Customer"&"*"))
Try one of these...

Use cells to hold the criteria:

A2 = Customer
B2 = New England

If you're using Excel 2007 or later:

=COUNTIFS(D2:D201,"*"&A2&"*",I2:I201,"*"&B2&"*")

This one will work in all versions of Excel:

=SUMPRODUCT(--(ISNUMBER(SEARCH(A2,D2:D201))),--(ISNUMBER(SEARCH(B2,I2:I201))))

3. ## Re: Compound countif statement

Which version of Excel are you using?

4. ## Re: Compound countif statement

I am using 2010

5. ## Re: Compound countif statement

There is not much to be added after Biff's recommedation.
I would rather make my ranges absolute(\$\$) but unless you will not drag your formulas down or accross T.Valko examples are ideal.

## User Tag List

#### Posting Permissions

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