SUMPRODUCT except for

[Guest]

I need to create a formula that will count everything in a column except for
4 items (Medicare, Medicaid, Self Pay and blanks). I use =COUNTIF to get
seperate totals on all those but the blanks, ie.
=COUNTIF(Sheet1!B2:B400,"MEDICARE") etc. But now I need to count ALL the
other insurance names that are not one of those 3 and is not a blank. So I
need something that will sum everything in column B excecpt for those 4 that
I listed above.

Any help would be greatly appreciated.

 

[Bob Phillips]

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(A2:A20,{"Medicare","Medicaid","Self
Pay"},0)))))-COUNTBLANK(A2:A20)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you for the reply Bob,
I used your formula (just changed the range to K2:K350), it came up with 84.
My totals so far for this month are Medicare 109, Medicaid 13, Self Pay 39
(I have formula's for those totals). By sorting and counting all other
insurance and blanks, I get 30 blanks, and 45 other insurance, and the answer
I am wanting this formula to calculate is the 45 (all other insurance). So
not sure why your formula came up with 84. Any suggestions?

 

[Guest]

Bob,
Disregard my previous post. Your formula DID work, it was the operator of
it (ME) that didn't put it in right. Thank you so much for all your help.

 

[Bob Phillips]

It was the wrap-around, the bane of the public newsgroups.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I have one more question. Sorry that I forgot to ask in my original post.
My formula now looks like this
=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(August!K2:K350,{"MEDICARE","MEDICAID","SELF PAY"},0)))))-COUNTBLANK(August!K2:K350)

If I want to add one more criteria to the above formula, i.e.
(August!D2350="SMITH MEMORIAL")
Can I work that into my formula?

Thank you again for all your help.

 

[Bob Phillips]

I guess that you mean

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(August!K2:K350,{"MEDICARE","MEDICAID","SELF
PAY"},0)))),
--(August!D2350="SMITH MEMORIAL"))-COUNTBLANK(August!K2:K350)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Yes, that is what I meant, I wasn't sure where to put it in the formula.

Again, thank you very much.