sum with three criteria

[Guest]

I am trying to sum a column if three criteria are met in one column. In
other words if Colum L has an EB, F, and EF then sum these in Column BB.
Here is what I have but this doesn't seem to work. I'm wondering if it is
because the criteria is all in the same column. I have used Sumproduct on
three different columns but not where the criteria is all in the same column.
Can someone help?

=SUMPRODUCT($L51:$L60="FB")*($L51:$L60="EB")*($L51:$L60="F")*(BB51:BB60)

 

[Guest]

Two problems I believe. First, sumproduct used this way takes the AND of the
criteria, not the OR. Since you're using the same column and different
values, the AND couldn't be satisfied. Second, you would still need to apply
the -- operation to convert the true/false arrays to values that can be used
by sumproduct.
Since you don't have multiple criteria that need to be simultaneously
satisfied, but rather three distinct criteria, I'd use three sumifs
=sumif($L51:$L60,"FB",$BB51:$BB60)+sumif($L51:$L60,"EB",$BB51:$BB60)+sumif($L51:$L60,"F",$BB51:$BB60)

 

[Bob Phillips]

=SUMPRODUCT(($L51:$L60={"FB","EB","F"})*(B51:B60))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Sorry, should have been

=SUMPRODUCT(($L51:$L60={"FB","EB","F"})*(BB51:BB60))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Aladin Akyurek]

Some options:

[1]

=SUM(SUMIF($L51:$L60,{"FB","EB","F"},$BB$51:$BB$60))

If the criterion values are all in some range, say, X2:X4...

[2a]

=SUMPRODUCT(--ISNUMBER(MATCH($L51:$L60,{"FB","EB","F"},0)),$BB$51:$BB$60)

[2b]

=SUMPRODUCT(SUMIF($L51:$L60,X2:X4,$BB$51:$BB$60))