Excel - countif/sumif with multiple variables

Asked By Carl Schreiner on 09-Mar-10 02:18 PM
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 replied to Carl Schreiner on 09-Mar-10 02:33 PM
The best idea is to use a Pivot Table. Find out all about them here:
http://www.cpearson.com/excel/pivots.htm

Regards,
Fred
Quesohotmail com> replied to Carl Schreiner on 09-Mar-10 02:45 PM
If you use a helper column off to the far right outside the print area (let us
use F for now) and have that helper column =C+A+B (so it equals
ID+person+date), then you will 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 is 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 replied to Quesohotmail com> on 09-Mar-10 06:31 PM
I like the idea of creating a unique identifier for each transaction but I
did not think it was possible to combine dates, numbers and names.  I am
getting a #value error when I try.  How should I address this?  Thanks for
your help! I am 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 replied to Carl Schreiner on 10-Mar-10 03:02 AM
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
Quesohotmail com> replied to Carl Schreiner on 10-Mar-10 02:16 PM
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.