SUMIF but ignore if duplicate in columns

[jsavoia]

I have a spreadsheet that looks like this:

A B C D
100 100 4.00 AR
100 4.00 AR


I need a formula that will Sum column C if column D is "AR" and if colulmn A is "100" but not to count it if "100" also appears in column B.

Help!

Thanks, JoAnn

 

[Haseeb Avarakkan]

Hi, Try

=SUMPRODUCT(--(A2:A100=100),--(B2:B100<>100),--(D2:D100="AR"),C2:C100)

Or,

Enter in

E2, 100
E3, AR

Then

=SUMPRODUCT(--(A2:A100=E2),--(B2:B100<>E2),--(D2:D100=E3),C2:C100)

 

[jsavoia]

Thank you! That did work, however I have discovered that my problem is a little more complicated.


A B C D E F
100 100 4.00 AR
102 100 4.00 AR

I need a formula that says sum column E if column F says "AR" and if A:D says "100" but don't count the row twice if a duplicate appears in that range. So E would be counted once for both row 1 and 2.

Thanks

 

[T. Valko]

Thank you! That did work, however I have discovered that my problem is a little more complicated.


A B C D E F
100 100 4.00 AR
102 100 4.00 AR

I need a formula that says sum column E if column F says "AR" and if A:D says "100" but don't count the row twice if a duplicate appears in that range. So E would be counted once for both row 1 and 2.

Thanks

Let's assume this is your data:

Book1
	A	B	C	D	E	F
1
2	100	56	96	28	47	AR
3	94	100	52	8	70	AR
4	56	72	27	0	5	AR
5	12	68	53	42	28	AR
6	19	87	10	100	45	VR
7	100	48	66	37	24	BR
8	10	81	25	96	25	AR
9	43	30	14	77	36	CR
10	100	100	100	100	73	AR
Sheet1

=SUMPRODUCT(--(MMULT(--(A2:D10=100),{1;1;1;1})>0),--(F2:F10="AR"),E2:E10)

 

[Jaysix]

Let's assume this is your data:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"></TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">100</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">56</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">96</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">28</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">47</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">AR</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">94</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">100</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">52</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">8</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">70</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">AR</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">56</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">72</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">27</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">AR</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">12</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">68</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">53</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">42</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">28</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">AR</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">19</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">87</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">10</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">100</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">45</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">VR</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">100</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">48</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">66</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">37</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">24</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">BR</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">10</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">81</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">25</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">96</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">25</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">AR</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">43</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">30</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">14</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">77</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">36</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">CR</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">100</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">100</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">100</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">100</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">73</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">AR</TD></TR></TBODY></TABLE>


=SUMPRODUCT(--(MMULT(--(A2:D10=100),{1;1;1;1})>0),--(F2:F10="AR"),E2:E10)

I think this formula type will work for something that I am working on (sumif two criteria do not sum duplicates).

My data is as follows:
PO: WO: Total PO Cost: Total WO Cost:
P17393 C216 $4288 ?
P17393 C216 $4288
P17393 C216 $4288
P17384 C216 $1000

The answer for this should $5288 for Total WO Cost. Can this formula get me there?

 

[Jaysix]

I figured this out with a dummie countifs (excel 2010 (two criteria)), dividing in same dummie cell, and then in main cell doing a sumif with only one criteria needed.

 

[T. Valko]

I think this formula type will work for something that I am working on (sumif two criteria do not sum duplicates).

My data is as follows:
PO: WO: Total PO Cost: Total WO Cost:
P17393 C216 $4288 ?
P17393 C216 $4288
P17393 C216 $4288
P17384 C216 $1000

The answer for this should $5288 for Total WO Cost. Can this formula get me there?

I figured this out with a dummie countifs (excel 2010 (two criteria)), dividing in same dummie cell, and then in main cell doing a sumif with only one criteria needed.

We can probably come up with something that's a bit easier!

What are the 2 criteria?

 

[Jaysix]

The two criteria are the the PO and WO columns. I am very interested in an alternate way to do this; I am still in the testing phase of the way that I figured out yesterday and it would be great to have a backup plan.

 

[T. Valko]

The two criteria are the the PO and WO columns. I am very interested in an alternate way to do this; I am still in the testing phase of the way that I figured out yesterday and it would be great to have a backup plan.

Try this...

Get the sum of unique POs for WO C216...

Book1
	A	B	C	D	E	F
1	PO:	WO:	Total PO Cost:	_	WO	Sum
2	P17393	C216	$4,288	_	C216	$9,288
3	P17393	C216	$4,288	_	_	_
4	P17393	C216	$4,288	_	_	_
5	P17384	C216	$1,000	_	_	_
6	P99999	X216	$3,000	_	_	_
7	P99999	C216	$500	_	_	_
8	X12345	W111	$1,000	_	_	_
9	Z44444	C216	$3,500	_	_	_
10	D00001	X456	$7,500	_	_	_
Sheet1

This array formula** entered in F2:

=SUM(IF(FREQUENCY(IF(B2:B10=E2,MATCH(A2:A10&B2:B10,A2:A10&B2:B10,0)),ROW(A2:A10)-ROW(A2)+1),C2:C10))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Jaysix]

Biff,
This formula is simply amazing. Seriously. Thank you.