sum with three criteria
 

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)

sum with three criteria
 

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 )

"Rob" wrote:

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)

sum with three criteria
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob" wrote in message
...
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)

sum with three criteria
 

Sorry, should have been

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rob" wrote in message
...
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)

sum with three criteria
 

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$5 1:$BB$60)

[2b]

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


Rob wrote:
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)