Sum data based on multiple criteria

M

Mady

My data is set up in three columns: cost center, description, an
value. I want to sum the value for each cost center where th
description contains a keyword anywhere in the text.

For example:

A1: Cost Center B1: Description C1:
Value
A2: 2615 B2: Arm gun still water 12345 C2: $25.00
A3: 2615 B3: Arm gun still water 12345 C3: $25.00
A4: 2645 B4: Arm gun still water 12345 C4: $25.00

I want to return the sum value for cost center 2615 where th
description contains the word gun. The sum value for this example i
$50.00.

Any help is appreciated.

Thanks,
Mad
 
D

Don Guillett

=SUMPRODUCT(--NOT(ISERR(SEARCH("*gun*",L1:L5)))*1)
to count gun in l1:l5
=SUMPRODUCT(--NOT(ISERR(SEARCH("*gun*",L1:L5)))*K1:K5)
to total k1:k5 where l1:l5 contains gun
 
M

Mady

Thank you for your response.
The formula you provided does not work for the data I want to pull.
Using your formula, in the example I gave, would give an end result o
$75.00. It only looks at the description field and sums everythin
that meets the criteria. I need it to look at the cost center field a
well.
Do you know how I could edit your formula so the end result would b
based on both criteria?
Again, thanks for your help.

Mad
 
D

Domenic

Hi Mady,

Using your example, try the following:

Enter your list of cost centers in Column A, say starting at A15.

Then enter the following forumula in B15 and copy down as far as yo
need to:

=SUMPRODUCT(($A$2:$A$10=A15)*(ISNUMBER(SEARCH("gun",$B$2:$B$10))),$C$2:$C$10)

Hope this helps
 
Top