SUMPRODUCT except for
 

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.

SUMPRODUCT except for
 

=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)

"Sapphyre" wrote in message
...
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.

SUMPRODUCT except for
 

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?

"Bob Phillips" wrote:

=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)

"Sapphyre" wrote in message
...
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.

SUMPRODUCT except for
 

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.


"Sapphyre" wrote:

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?

"Bob Phillips" wrote:

=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)

"Sapphyre" wrote in message
...
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.

SUMPRODUCT except for
 

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)

"Sapphyre" wrote in message
...
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.


"Sapphyre" wrote:

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?

"Bob Phillips" wrote:

=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)

"Sapphyre" wrote in message
...
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.

SUMPRODUCT except for
 

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","ME DICAID","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.

"Bob Phillips" wrote:

=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)

"Sapphyre" wrote in message
...
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.

SUMPRODUCT except for
 

I guess that you mean

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


--
HTH

Bob

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

"Sapphyre" wrote in message
...
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","ME DICAID","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.

"Bob Phillips" wrote:

=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)

"Sapphyre" wrote in message
...
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.

SUMPRODUCT except for
 

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

Again, thank you very much.

"Bob Phillips" wrote:

I guess that you mean

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


--
HTH

Bob

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

"Sapphyre" wrote in message
...
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","ME DICAID","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.

"Bob Phillips" wrote:

=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)

"Sapphyre" wrote in message
...
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.