Excel - COUNTIFS - 2 different scenarios to count

Asked By Roady

04-Feb-10 10:36 AM

Hi:

I have tried many variations of different nested formulas and have had no
luck solving this one.

Here are the two scenarios that I am trying to accomplish:
Scenario 1:
Count 1 if the following criteria are met on a single line of data:
Col F contains text (is not blank)
Col G, H, and I do NOT contain "Dropped"

Scenario 2:
Count 1 if the following criteria are met on a single line of data:
Col F contains text (is not blank)
Col G, H, I all contain the word "Approved" or "N/A" - the tricky part is
that there are several different variations of Approved so I tried using the
wildcard "*Approved*" but it does not seem to work in this particular formula.

The two scenarios are separate from eachother. Any help is appreciated!
Thanks.

04-Feb-10 11:23 AM

Scenario 1:
=SUMPRODUCT(--ISTEXT(F2:F10),--ISERROR(SEARCH("dropped",G2:G10)),--ISERROR(SEARCH("dropped",H2:H10)),--ISERROR(SEARCH("dropped",I2:I10)))

Scenario 2:
=SUMPRODUCT(--ISTEXT(F2:F10),
(ISNUMBER(SEARCH("approved",G2:G10))+ISNUMBER(SEARCH("n/a",G2:G10))),
(ISNUMBER(SEARCH("approved",H2:H10))+ISNUMBER(SEARCH("n/a",H2:H10))),
(ISNUMBER(SEARCH("approved",I2:I10))+ISNUMBER(SEARCH("n/a",I2:I10))))

Note that the SEARCH function is non-case sensitive. If you want to switch
it to case sensitive, use the FIND function. If column F may contain values
and not just text, change that arguement to:
--NOT(ISBLANK(F2:F10))

Finally, unless using XL2007, you cannot callout entire columns using
SUMPRODUCT.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

09-Feb-10 05:06 PM

One follow-up question regarding your last comment, "Finally, unless using
XL2007, you cannot callout entire columns using SUMPRODUCT" - So, the grid
that I am creating could potentially be used by both 2003 and 2007 users.
Instead of going until row 65536, can I just go to 65535 and then the formula
will work properly?
What can I do to make sure it will work for both versions?

Thanks!