SUMPRODUCT except for

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sapphyre - 18 Jul 2007 14:16 GMT

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.

Reply to this Message

Bob Phillips - 18 Jul 2007 14:33 GMT

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

Signature

HTH

Bob

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

>I need to create a formula that will count everything in a column except
>for
[quoted text clipped - 8 lines]
>
> Any help would be greatly appreciated.

Reply to this Message

Sapphyre - 18 Jul 2007 15:34 GMT

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?

> =SUMPRODUCT(--(NOT(ISNUMBER(MATCH(A2:A20,{"Medicare","Medicaid","Self
> Pay"},0)))))-COUNTBLANK(A2:A20)
[quoted text clipped - 11 lines]
> >
> > Any help would be greatly appreciated.

Reply to this Message

Sapphyre - 18 Jul 2007 15:56 GMT

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.

> Thank you for the reply Bob,
> I used your formula (just changed the range to K2:K350), it came up with 84.
[quoted text clipped - 19 lines]
> > >
> > > Any help would be greatly appreciated.

Reply to this Message

Bob Phillips - 18 Jul 2007 16:35 GMT

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

Signature

HTH

Bob

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

> Bob,
> Disregard my previous post. Your formula DID work, it was the operator of
[quoted text clipped - 32 lines]
>> > >
>> > > Any help would be greatly appreciated.

Reply to this Message

Sapphyre - 18 Jul 2007 16:44 GMT

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!D2:D350="SMITH MEMORIAL")
Can I work that into my formula?

Thank you again for all your help.

> =SUMPRODUCT(--(NOT(ISNUMBER(MATCH(A2:A20,{"Medicare","Medicaid","Self
> Pay"},0)))))-COUNTBLANK(A2:A20)
[quoted text clipped - 11 lines]
> >
> > Any help would be greatly appreciated.

Reply to this Message

Bob Phillips - 18 Jul 2007 16:59 GMT

I guess that you mean

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

Signature

HTH

Bob

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

>I have one more question. Sorry that I forgot to ask in my original post.
> My formula now looks like this:
[quoted text clipped - 25 lines]
>> >
>> > Any help would be greatly appreciated.

Reply to this Message

Sapphyre - 18 Jul 2007 22:00 GMT

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

Again, thank you very much.

> I guess that you mean
>
[quoted text clipped - 31 lines]
> >> >
> >> > Any help would be greatly appreciated.

Reply to this Message

Rate this thread: