SUMIF with multiple conditional criteria?

[emustyle]

Hey All,

Am a first time poster but have found "lurking" to be extremely helpful for my "day-to-day" work! I think I've finally run into something that I can post about so hopefully its worthy of the mr.excel boards

I am trying to work with a SUMIF statement:
=SUM(SUMIF($C:$C,{"ABC","CBA","BCA",ACB"},D:D))

What It does is look in column C:C to see if there is matching criteria and sum the corresponding cells in the D:D range.
What I'd like to do is have my criteria (in the curly brackets) actually reference a cell so that I can make my criteria variable.

The end result I'm looking for is something a long the lines of =SUM(SUMIF($C:$C,{H3},D:D)) Where H3 is "ABC","CBA","BCA",ACB"

I've tried doing things like putting each criteria into its own cell, however everytime I attempt to reference a cell I get an excel error (The formula you typed contains an error)

I had looked at Sumifs but I believe (and I could be wrong) that all the criteria within SUMIFs use an "AND" operator vs. an "OR" operator which I'm seeking.

Also if there's a more efficient way of doing this, I would also entertain completely changing my formulas!

Any help you guys can provide, would be greatly appreciated

 

[Jonmo1]

Welcome to the board

Try
=SUMPRODUCT(SUMIF($C:$C,$G$1:$G$4,D:D))

G1 = ABC
G2 = CBA
G3 = BCA
G4 = ACB


Hope that helps.

 

[emustyle]

That's Perfect Jonmo1!!!

Very simple too! Hopefully I can return the favor someday!

 

[Jonmo1]

Glad to help, thanks for the feedback.