sumif with>1 criteria

[Neets]

Hi, I'm attempting to sumif a selection if it contains the following field names: "Lost Order - AP" and "Received Order - AP"

The selection column is F and the summation column is E.

<TABLE style="WIDTH: 116pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=154><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=154><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 116pt; HEIGHT: 12.75pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl65 height=17 width=154>:=SUMIF(F2:F31,"Lost Order - AP, Received Order - AP",E2:E31)</TD></TR></TBODY></TABLE>

Is there an easy way to build this?

Thanks!

 

[MrKowz]

How could a cell contain both "Lost Order - AP" and "Received Order - AP"?

Are you wanting to sum up each group of them? If so, try adding two separate SUMIFs together:
=SUMIF(F2:F31,"Lost Order - AP",E2:E31)+SUMIF(F2:F31,"Received Order - AP",E2:E31)

 

[Aladin Akyurek]

Hi, I'm attempting to sumif a selection if it contains the following field names: "Lost Order - AP" and "Received Order - AP"

The selection column is F and the summation column is E.

<TABLE style="WIDTH: 116pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=154><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=154><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 116pt; HEIGHT: 12.75pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl65 height=17 width=154>:=SUMIF(F2:F31,"Lost Order - AP, Received Order - AP",E2:E31)</TD></TR></TBODY></TABLE>

Is there an easy way to build this?

Thanks!

Try...

=SUM(SUMIF(F2:F31,{"Lost Order - AP","Received Order - AP"},E2:E31))

if my assumption is correct about the two criteria.

 

[T. Valko]

Hi, I'm attempting to sumif a selection if it contains the following field names: "Lost Order - AP" and "Received Order - AP"

The selection column is F and the summation column is E.

<TABLE style="WIDTH: 116pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=154 border=0><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=154><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 116pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=154 height=17>:=SUMIF(F2:F31,"Lost Order - AP, Received Order - AP",E2:E31)</TD></TR></TBODY></TABLE>

Is there an easy way to build this?

Thanks!

One way...

Use cells to hold the criteria:

• A2 = Lost Order - AP
• B2 = Received Order - AP

Then:

=SUMPRODUCT(--(ISNUMBER(MATCH(F2:F31,A2:B2,0))),E2:E31)

 

[Neets]

Perfect! Thanks!!!

 

[MrKowz]

Perfect! Thanks!!!

Cheers! Thank you for the feedback.