Summarizing values from different worksheets to one summary worksheet

**lkim** · 01-03-2012, 03:14 PM

Happy New Year!
I have two worksheets showing shopping cost summary for Person A and Person B. Each person enters the price of the item they purchase as the purchases are being made so there will be duplicate item entries with different prices (i.e. an eraser costs $4 in shopA but $3 in shopB, these values were entered separatedly)

I have a cost Summary worksheet that list all the items these two shoppers will eventually purchase. How can I summarize the inputs from PersonA and PersonB's shopping list in the cost summary sheet without going through each item and adding the cost one by one as I've done here?

Thanks!

**DBY** · 01-03-2012, 04:21 PM

Hello
Two Sumif functions should do what you're looking for. For example in your sample file:

For person A

=SUMIF(PersonA!$A$2:$A$13,$A2,PersonA!$B$2:$B$13)

copied down column B

For person B

=SUMIF(PersonB!$A$2:$A$17,$A2,PersonB!$B$2:$B$17)

copied down column C

Hope this helps.

**lkim** · 01-03-2012, 06:00 PM

That code solved that problem. THanks!
Now, if I didn't have all the items listed in the summary sheet and want to use the user inputs (PersonA and PersonB's shopping list) to generate the unique item list in the summary sheet, how would I do that? Since they are entering the same item name multiple times, I used pivot table to get unique list from each worksheet and combine the list and do the second pivot table to create the final list, but it seems like a long process.

Thank you for your help!

**DBY** · 01-04-2012, 03:54 PM

Hello again

I've been working on the problem you mentioned and without using filters or pivot tables the only way I Know to create unique lists is with Array Formulas. Please see attached Workbook as an example.

**DBY** · 01-05-2012, 08:06 AM

Hello again
I've made an amendment to the previous attached file. I Should have realized that the two helper columns on the CostSummary Sheet: PersonA Items and PersonB Items, are not needed, the formulas in Column A work without them. The items can be referenced directly with two dynamic named ranges. Please see new attachment.

I hope I'm not confusing you.