Microsoft Excel - Formulas - Asked By Lisa G on 07-Apr-11 11:53 AM

I need help with a formula. I need to obtain a count on one page, based on values on another page. I need a count if the value in column H is <3 and if the value in column O is NOT 02, 07, 20 (out of 11 possible values listed in column O). Can anyone help?

Thanks!!!
wally eye replied to Lisa G on 07-Apr-11 12:30 PM
You could use the countifs, in '07 and on:

=countifs(H2:H99,"<3",o2:o99,"<>02", o2:o99,"<>07", o2:o99,"<>20")

or a sumproduct:

=SUMPRODUCT(N(H2:H99<3),N(NOT(ISNA(MATCH(o2:o99,{"02","07","20"},0)))))

The MATCH function will return an #N/A for each value in o2:o99 that isn't in your list, ISNA will convert the #N/A to a true or false, the NOT will reverse that, and the N() will convert the true/false to a 1/0.  It looks like your values are text, so I've enclosed them in double-quotes.  If they are numbers, exclude the double quotes.  Also, please not the "brackets" around the list are actually ellispsis, not parenthesis.

Even through it is a bit more involved, I like the sumproduct version better because it will scale up nicely.  That is, if you want to add more values, you only have to add them to the short list, not add a new set of parameters.
Jackpot . replied to Lisa G on 07-Apr-11 01:01 PM
Hi Lisa


Try

=SUMPRODUCT((H2:H99<>"")*(H2:H99<3)*(ISNA(MATCH(O2:O99, {2,7,20},0))))

OR with the possible values in A1:A11

=SUMPRODUCT((H2:H99<>"")*(H2:H99<3)*(ISNA(MATCH(O2:O99,A1:A11,0))))