Aggregating Data

0

I have a large table with the number of patient encounters in Column A, and

a listing of procedure codes in Columns B:D.  Unique procedure codes may

appear in multiple rows, but will only appear once in each row.  For

example, in Row 1, I may have 10 encounters with Procedure Codes P1, P2, and

P3 in columns B:D, respectively.  In Row 2, may show 20 encounters with

Procedure Codes P1, and P4 in Columns B:C (Column D is blank).  I am trying

to build a formula that will add the number of encounters (from Column A)

for each row that contains P1.  Ultimately, I want to be able to get the

totals (summing Column A values), for all Procedures P1, P2, P3, Pn.

Greatly appreciate any help you can offer.

Answer
Discuss

Discussion

I suggest you create a workbook with two sheets. One to show the data as they are and another to show the result you want to achieve. Upload the workbook as an attachment to your question.
Variatus (rep: 2205) Sep 22, '17 at 9:12 pm
Add to Discussion

Answers

0
Selected Answer

This formula ought to do the job. Place it in C12 (for example) and copy down.

=SUMPRODUCT($A$2:$A$5*($B$2:$D$5=$A12))
Discuss

Discussion

Variatus you are a genius!  Thank you very much for you time and assistance. Works perfectly. 
pep (rep: 2) Sep 26, '17 at 11:16 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login