countif with multiple conditions

[vancoservices]

Hello everyone,

I am wondering if it is possible to use the countif function or any other ones to take into account multiple conditions? Here is my example and what I am trying to calculate:

I have 1000 records and column A = Industry, column B = product.

I am trying to count the number of records that fall within the following 3 industries and 3 products:

Industry: Sporting Goods, Law Firm, Fitness Club
Product: Phone, Cable, Internet

Any combination of the 3 is what I'm trying to count (ex. SG and Phone, LF and Internet, FC and Phone, SG and Internet, etc.).

If I use the countif function i will have to do 9 statements to cover every example and I don't want to do that. Is there a better formula/function/way to get this? Please help!!!

[martindwilson]

have you tried countifs ?

[DonkeyOte]

Given the OR nature of the condition, perhaps:

=SUMPRODUCT(--ISNUMBER(SEARCH({"Sporting Goods","Law Firm","Fitness Club"},A1:A1000)),--ISNUMBER(SEARCH({"Cable","Phone","Internet"},B1:B1000)))

[scottwhittaker2333]

If you are using excel 2007 you can use countifs

[DonkeyOte]

Any combination of the 3 is what I'm trying to count (ex. SG and Phone, LF and Internet, FC and Phone, SG and Internet, etc.).

If I use the countif function i will have to do 9 statements to cover every example and I don't want to do that.

If you are using excel 2007 you can use countifs

@sscottwhittaker2333: you would still need 9 separate COUNTIFS given the AND nature of the function.

Given the OP initial post I would say that actually precludes COUNTIFS as a viable option.

[vancoservices]

DonkeyOte is right, countifs does not work, I tried that a long time ago. It would work if I only wanted 1 variable in each column but I have 3 so it returns an incorrect number.

I tried =SUMPRODUCT(--ISNUMBER(SEARCH({"Sporting Goods","Law Firm","Fitness Club"},A1:A1000)),--ISNUMBER(SEARCH({"Cable","Phone","Internet"},B1:B1000))) but it gave me an incorrect value as well. It was close but it looked like it wasn't including one of the variables.

[DonkeyOte]

I tried...[SUMPRODUCT formula]...but it gave me an incorrect value as well

Please post a sample file illustrating as such.

edit: hang on - yes you're right that won't work...

[DonkeyOte]

This extended version should work

Please Login or Register  to view this content.

in the above H1:I3 contain column A & B criterion

I confess I am suffering something of a brain freeze at present re: shortened version

[martindwilson]

what's wrong with sum 9 countifs? once its done its done

[DonkeyOte]

If you don't need to conduct a SEARCH (ie no partial matching required) such that a MATCH can be used then:

=SUMPRODUCT(ISNUMBER(MATCH(A1:A1000,H1:H3,0))*ISNUMBER(MATCH(B1:B1000,I1:I3,0)))

should work (where H1:I3 contain criterion)

Not sure why I went down the SEARCH route in truth - nothing in the thread thus far implies it's required.

[vancoservices]

The sumproduct(sign(isnumber(search worked! There isn't any partial matching so I will for sure try the formula with match.

Now to add more confusion...... If in the same example there is a column C called Upgrade Y/N and I wanted to add that into the equation, how should I add that? (searching for the same thing I did before but only counting records with a "Y")

[DonkeyOte]

Using the MATCH example for sake of simplicity...

=SUMPRODUCT((C1:C1000="Y")*ISNUMBER(MATCH(A1:A1000,H1:H3,0))*ISNUMBER(MATCH(B1:B1000,I1:I3,0)))

[vancoservices]

DonkeyOte you're awesome! Problem completely solved!!!! Thanks for all your help!