countif/sumif with multiple variables

I am working on sorting through a database of medical CPT codes and would
like to get the total number of proocedure codes with the total amount of
reimbursement for each code. If been able to use countif and sumif to get
the totals but there are mutiple cases where there are mutiple reimbursements
for a given date so the number of codes is skewed high. I would like to
total the code and reimbursement ONLY if the name, date, and code all match:

name date code $
jane 3/1/2009 99204 50
joe 3/1/2009 99204 25
jane 3/1/2009 99204 30
joe 3/2/2009 31235 150
mark 3/3/2009 31235 140
sue 3/3/2009 31237 200

expected result:
code total $
99204 2 105
31235 2 190
31237 1 200

note that the total for 99204 is 2 instead of 3 because the name and date
were the same. Any ideas?

The best idea is to use a Pivot Table. Find out all about them he
http://www.cpearson.com/excel/pivots.htm

Regards,
Fred

"Carl Schreiner" > wrote in message
...
>I am working on sorting through a database of medical CPT codes and would
> like to get the total number of proocedure codes with the total amount of
> reimbursement for each code. If been able to use countif and sumif to get
> the totals but there are mutiple cases where there are mutiple
> reimbursements
> for a given date so the number of codes is skewed high. I would like to
> total the code and reimbursement ONLY if the name, date, and code all
> match:
>
> name date code $
> jane 3/1/2009 99204 50
> joe 3/1/2009 99204 25
> jane 3/1/2009 99204 30
> joe 3/2/2009 31235 150
> mark 3/3/2009 31235 140
> sue 3/3/2009 31237 200
>
> expected result:
> code total $
> 99204 2 105
> 31235 2 190
> 31237 1 200
>
> note that the total for 99204 is 2 instead of 3 because the name and date
> were the same. Any ideas?
>

If you use a helper column off to the far right outside the print area (let's
use F for now) and have that helper column =C+A+B (so it equals
ID+person+date), then you'll have a unique identifier for each transaction.

Using your 6-row sample data below in rows 2:7, then
F2 =C2+A2+B2
then use your fill handle to drag that formula on down.

Assuming your expected result table begins with 99204 in cell A10,
B10 =SUM(($C$2:$C$7=$A10)/COUNTIF($F$2:$F$7,$F$2:$F$7&""))
Now that is an array formula and must be committed using Ctrl+Shift+Enter,
not just Enter. If you do it right, Excel will put curly braces {} around
the formula for you.

Then C10 is a normal formula (so just use Enter to commit it):
C10 =SUMIF($C$2:$C$7,$A10,$D$2:$D$7)

You'll notice that 31235 returns an answer of $290, not $190, but I believe
that's the correct answer based on your sample data.

Just modify the above ranges to what you need, but be sure to keep dollar
signs so that you can then use your fill handle to drag formulas down as far
as needed.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Carl Schreiner" wrote:

> I am working on sorting through a database of medical CPT codes and would
> like to get the total number of proocedure codes with the total amount of
> reimbursement for each code. If been able to use countif and sumif to get
> the totals but there are mutiple cases where there are mutiple reimbursements
> for a given date so the number of codes is skewed high. I would like to
> total the code and reimbursement ONLY if the name, date, and code all match:
>
> name date code $
> jane 3/1/2009 99204 50
> joe 3/1/2009 99204 25
> jane 3/1/2009 99204 30
> joe 3/2/2009 31235 150
> mark 3/3/2009 31235 140
> sue 3/3/2009 31237 200
>
> expected result:
> code total $
> 99204 2 105
> 31235 2 190
> 31237 1 200
>
> note that the total for 99204 is 2 instead of 3 because the name and date
> were the same. Any ideas?
>

I like the idea of creating a unique identifier for each transaction but I
didn't think it was possible to combine dates, numbers and names. I'm
getting a #value error when I try. How should I address this? Thanks for
your help! I'm obviously a little new at these formulas and also should have
used a spreadsheet instead of my head to add 140+150! ;o)

"Queso" wrote:

> If you use a helper column off to the far right outside the print area (let's
> use F for now) and have that helper column =C+A+B (so it equals
> ID+person+date), then you'll have a unique identifier for each transaction.
>
> Using your 6-row sample data below in rows 2:7, then
> F2 =C2+A2+B2
> then use your fill handle to drag that formula on down.
>
> Assuming your expected result table begins with 99204 in cell A10,
> B10 =SUM(($C$2:$C$7=$A10)/COUNTIF($F$2:$F$7,$F$2:$F$7&""))
> Now that is an array formula and must be committed using Ctrl+Shift+Enter,
> not just Enter. If you do it right, Excel will put curly braces {} around
> the formula for you.
>
> Then C10 is a normal formula (so just use Enter to commit it):
> C10 =SUMIF($C$2:$C$7,$A10,$D$2:$D$7)
>
> You'll notice that 31235 returns an answer of $290, not $190, but I believe
> that's the correct answer based on your sample data.
>
> Just modify the above ranges to what you need, but be sure to keep dollar
> signs so that you can then use your fill handle to drag formulas down as far
> as needed.
> --
> Please remember to indicate when the post is answered so others can benefit
> from it later.
>
>
> "Carl Schreiner" wrote:
>
> > I am working on sorting through a database of medical CPT codes and would
> > like to get the total number of proocedure codes with the total amount of
> > reimbursement for each code. If been able to use countif and sumif to get
> > the totals but there are mutiple cases where there are mutiple reimbursements
> > for a given date so the number of codes is skewed high. I would like to
> > total the code and reimbursement ONLY if the name, date, and code all match:
> >
> > name date code $
> > jane 3/1/2009 99204 50
> > joe 3/1/2009 99204 25
> > jane 3/1/2009 99204 30
> > joe 3/2/2009 31235 150
> > mark 3/3/2009 31235 140
> > sue 3/3/2009 31237 200
> >
> > expected result:
> > code total $
> > 99204 2 105
> > 31235 2 190
> > 31237 1 200
> >
> > note that the total for 99204 is 2 instead of 3 because the name and date
> > were the same. Any ideas?
> >

Hi Carl

to create the unique identifier you need to concatenate the values, not
add them
=A1&B1&C1

I always like to use the pipe character | between each of my
concatenations to be sure there is no confusion
=A1&"|"&B1&"|"&C1
--
Regards
Roger Govier

Carl Schreiner wrote:
> I like the idea of creating a unique identifier for each transaction but I
> didn't think it was possible to combine dates, numbers and names. I'm
> getting a #value error when I try. How should I address this? Thanks for
> your help! I'm obviously a little new at these formulas and also should have
> used a spreadsheet instead of my head to add 140+150! ;o)
>
> "Queso" wrote:
>
>> If you use a helper column off to the far right outside the print area (let's
>> use F for now) and have that helper column =C+A+B (so it equals
>> ID+person+date), then you'll have a unique identifier for each transaction.
>>
>> Using your 6-row sample data below in rows 2:7, then
>> F2 =C2+A2+B2
>> then use your fill handle to drag that formula on down.
>>
>> Assuming your expected result table begins with 99204 in cell A10,
>> B10 =SUM(($C$2:$C$7=$A10)/COUNTIF($F$2:$F$7,$F$2:$F$7&""))
>> Now that is an array formula and must be committed using Ctrl+Shift+Enter,
>> not just Enter. If you do it right, Excel will put curly braces {} around
>> the formula for you.
>>
>> Then C10 is a normal formula (so just use Enter to commit it):
>> C10 =SUMIF($C$2:$C$7,$A10,$D$2:$D$7)
>>
>> You'll notice that 31235 returns an answer of $290, not $190, but I believe
>> that's the correct answer based on your sample data.
>>
>> Just modify the above ranges to what you need, but be sure to keep dollar
>> signs so that you can then use your fill handle to drag formulas down as far
>> as needed.
>> --
>> Please remember to indicate when the post is answered so others can benefit
>> from it later.
>>
>>
>> "Carl Schreiner" wrote:
>>
>>> I am working on sorting through a database of medical CPT codes and would
>>> like to get the total number of proocedure codes with the total amount of
>>> reimbursement for each code. If been able to use countif and sumif to get
>>> the totals but there are mutiple cases where there are mutiple reimbursements
>>> for a given date so the number of codes is skewed high. I would like to
>>> total the code and reimbursement ONLY if the name, date, and code all match:
>>>
>>> name date code $
>>> jane 3/1/2009 99204 50
>>> joe 3/1/2009 99204 25
>>> jane 3/1/2009 99204 30
>>> joe 3/2/2009 31235 150
>>> mark 3/3/2009 31235 140
>>> sue 3/3/2009 31237 200
>>>
>>> expected result:
>>> code total $
>>> 99204 2 105
>>> 31235 2 190
>>> 31237 1 200
>>>
>>> note that the total for 99204 is 2 instead of 3 because the name and date
>>> were the same. Any ideas?
>>>

Doh! Thanks for pointing out my error! Like Roger said, use ampersands to
concatenate, not plus signs.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Carl Schreiner" wrote:

> I like the idea of creating a unique identifier for each transaction but I
> didn't think it was possible to combine dates, numbers and names. I'm
> getting a #value error when I try. How should I address this? Thanks for
> your help! I'm obviously a little new at these formulas and also should have
> used a spreadsheet instead of my head to add 140+150! ;o)
>
> "Queso" wrote:
>
> > If you use a helper column off to the far right outside the print area (let's
> > use F for now) and have that helper column =C+A+B (so it equals
> > ID+person+date), then you'll have a unique identifier for each transaction.
> >
> > Using your 6-row sample data below in rows 2:7, then
> > F2 =C2+A2+B2
> > then use your fill handle to drag that formula on down.
> >
> > Assuming your expected result table begins with 99204 in cell A10,
> > B10 =SUM(($C$2:$C$7=$A10)/COUNTIF($F$2:$F$7,$F$2:$F$7&""))
> > Now that is an array formula and must be committed using Ctrl+Shift+Enter,
> > not just Enter. If you do it right, Excel will put curly braces {} around
> > the formula for you.
> >
> > Then C10 is a normal formula (so just use Enter to commit it):
> > C10 =SUMIF($C$2:$C$7,$A10,$D$2:$D$7)
> >
> > You'll notice that 31235 returns an answer of $290, not $190, but I believe
> > that's the correct answer based on your sample data.
> >
> > Just modify the above ranges to what you need, but be sure to keep dollar
> > signs so that you can then use your fill handle to drag formulas down as far
> > as needed.
> > --
> > Please remember to indicate when the post is answered so others can benefit
> > from it later.
> >
> >
> > "Carl Schreiner" wrote:
> >
> > > I am working on sorting through a database of medical CPT codes and would
> > > like to get the total number of proocedure codes with the total amount of
> > > reimbursement for each code. If been able to use countif and sumif to get
> > > the totals but there are mutiple cases where there are mutiple reimbursements
> > > for a given date so the number of codes is skewed high. I would like to
> > > total the code and reimbursement ONLY if the name, date, and code all match:
> > >
> > > name date code $
> > > jane 3/1/2009 99204 50
> > > joe 3/1/2009 99204 25
> > > jane 3/1/2009 99204 30
> > > joe 3/2/2009 31235 150
> > > mark 3/3/2009 31235 140
> > > sue 3/3/2009 31237 200
> > >
> > > expected result:
> > > code total $
> > > 99204 2 105
> > > 31235 2 190
> > > 31237 1 200
> > >
> > > note that the total for 99204 is 2 instead of 3 because the name and date
> > > were the same. Any ideas?
> > >