countif/sumif with multiple variables

[Carl Schreiner]

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?

 

[Fred Smith]

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

Regards,
Fred

 

[Queso hotmail com>]

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.

 

[Carl Schreiner]

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)

 

[Roger Govier]

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

 

[Queso hotmail com>]

Doh! Thanks for pointing out my error! Like Roger said, use ampersands to
concatenate, not plus signs.