Lookup issues

[PhatLamb]

Okay, I have a report that is exported from Quickbooks, and it lists
customer and then one to ten account categories and amounts for each
What I need to do is get the totals for each category totaled on
separate report. Each customer is different, one may use only on
category, another two or three.

Name Color Amount
Dinny Red $10
White $25
Blue $15

Donny Red $15
Yellow $20

Dunny Blue $35
Yellow $20


The report only cars about the category totals, and in theory, a looku
and match of some kind would produce the result below:


Red $25
White $25
Blue $50
Yellow $40

I can't figure it out, but I am only a hack at Excel, simple mat
functions, so any help is welcome

 

[JulieD]

Hi

not really sure how your data is laid out ... if you don't need the customer
name only the colour & amount, you could sort on colour and then use the
data / subtotal option.

or you could list your unique colours somewhere else on the sheet or on
another sheet (you could probably use the advance filter to get the unique
list so you don't have to type it) and then use the SUMIF function to add up
the amounts
e.g.
if your original list is in column A, B & C
and you've got your unquie list starting in E2 then
if F2 your formula would be
=SUMIF(B:B,E2,C:C)

Hope this helps
Cheers
JulieD

 

[Aladin Akyurek]

Account categories that appear to be colors... Hmm, possible.

SUMIF(ColorRange,Color,AmountRange)

like in:

=SUMIF($B$2:$B$100,"White",$C$2:$C$100)