Sum certain rows only

[drluke]

[TABLE="width: 656"]
<colgroup><col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;" span="3"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;" span="7"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;" span="2"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]F[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]A001[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]TRAD[/TD]
[TD="bgcolor: transparent, align: right"]270.24[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]A001[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]MEDIA[/TD]
[TD="bgcolor: transparent, align: right"]373.28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]A001[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]DELIV[/TD]
[TD="bgcolor: transparent, align: right"]221.19[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]A001[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]DISC[/TD]
[TD="bgcolor: transparent, align: right"]17.30[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]864.17[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]A001[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]TECH[/TD]
[TD="bgcolor: transparent, align: right"]559.98[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"]A001[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]ADCV[/TD]
[TD="bgcolor: transparent, align: right"]58.88[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"]A001[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]PNS[/TD]
[TD="bgcolor: transparent, align: right"]160.56[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"]A001[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]DESIGN[/TD]
[TD="bgcolor: transparent, align: right"]333.12[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"]A001[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]CONS[/TD]
[TD="bgcolor: transparent, align: right"]42.76[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"]A001[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]REBT[/TD]
[TD="bgcolor: transparent, align: right"]18.70[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]1155.30[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"]A002[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"]MEDIA[/TD]
[TD="bgcolor: transparent, align: right"]210.88[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"]A002[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"]DISC[/TD]
[TD="bgcolor: transparent, align: right"]25.14[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]210.88[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 13"]I need a sum formula in column F that will sum amounts in column D for all the same references in column B, if column A = A001 or A002[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 5"]but exclude amonts where col C = DISC or REBT. [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 10"]I would like the result to be in the same row that as the last occurrence of the reference in col B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 7"]For all rows in the database that does not equal A001 or A002 do nothing[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

I think your first amount should be 864.71 not 864.17
In F1:
=IF(AND(A1<>"",COUNTIFS($A$1:A1,A1,$B$1:B1,B1)=COUNTIFS(A:A,A1,B:B,B1)),SUMIFS($D$1:D1,$A$1:A1,A1,$B$1:B1,B1,$C$1:C1,"<>DISC",$C$1:C1,"<>REBT"),"")
Copy down.

Excel 2010
	A	B	C	D	E	F
1	A001	1	TRAD	270.24
2	A001	1	MEDIA	373.28
3	A001	1	DELIV	221.19
4	A001	1	DISC	17.3		864.71
5	A001	2	TECH	559.98
6	A001	2	ADCV	58.88
7	A001	2	PNS	160.56
8	A001	2	DESIGN	333.12
9	A001	2	CONS	42.76
10	A001	2	REBT	18.7		1155.3
11	A002	7	MEDIA	210.88
12	A002	7	DISC	25.14		210.88
13
Sheet1

Cell Formulas
Range	Formula
F1	=IF(AND(A1<>"",COUNTIFS($A$1:A1,A1,$B$1:B1,B1)=COUNTIFS(A:A,A1,B:B,B1)),SUMIFS($D$1:D1,$A$1:A1,A1,$B$1:B1,B1,$C$1:C1,"<>DISC",$C$1:C1,"<>REBT"),"")
F2	=IF(AND(A2<>"",COUNTIFS($A$1:A2,A2,$B$1:B2,B2)=COUNTIFS(A:A,A2,B:B,B2)),SUMIFS($D$1:D2,$A$1:A2,A2,$B$1:B2,B2,$C$1:C2,"<>DISC",$C$1:C2,"<>REBT"),"")
F3	=IF(AND(A3<>"",COUNTIFS($A$1:A3,A3,$B$1:B3,B3)=COUNTIFS(A:A,A3,B:B,B3)),SUMIFS($D$1:D3,$A$1:A3,A3,$B$1:B3,B3,$C$1:C3,"<>DISC",$C$1:C3,"<>REBT"),"")
F4	=IF(AND(A4<>"",COUNTIFS($A$1:A4,A4,$B$1:B4,B4)=COUNTIFS(A:A,A4,B:B,B4)),SUMIFS($D$1:D4,$A$1:A4,A4,$B$1:B4,B4,$C$1:C4,"<>DISC",$C$1:C4,"<>REBT"),"")
F5	=IF(AND(A5<>"",COUNTIFS($A$1:A5,A5,$B$1:B5,B5)=COUNTIFS(A:A,A5,B:B,B5)),SUMIFS($D$1:D5,$A$1:A5,A5,$B$1:B5,B5,$C$1:C5,"<>DISC",$C$1:C5,"<>REBT"),"")
F6	=IF(AND(A6<>"",COUNTIFS($A$1:A6,A6,$B$1:B6,B6)=COUNTIFS(A:A,A6,B:B,B6)),SUMIFS($D$1:D6,$A$1:A6,A6,$B$1:B6,B6,$C$1:C6,"<>DISC",$C$1:C6,"<>REBT"),"")
F7	=IF(AND(A7<>"",COUNTIFS($A$1:A7,A7,$B$1:B7,B7)=COUNTIFS(A:A,A7,B:B,B7)),SUMIFS($D$1:D7,$A$1:A7,A7,$B$1:B7,B7,$C$1:C7,"<>DISC",$C$1:C7,"<>REBT"),"")
F8	=IF(AND(A8<>"",COUNTIFS($A$1:A8,A8,$B$1:B8,B8)=COUNTIFS(A:A,A8,B:B,B8)),SUMIFS($D$1:D8,$A$1:A8,A8,$B$1:B8,B8,$C$1:C8,"<>DISC",$C$1:C8,"<>REBT"),"")
F9	=IF(AND(A9<>"",COUNTIFS($A$1:A9,A9,$B$1:B9,B9)=COUNTIFS(A:A,A9,B:B,B9)),SUMIFS($D$1:D9,$A$1:A9,A9,$B$1:B9,B9,$C$1:C9,"<>DISC",$C$1:C9,"<>REBT"),"")
F10	=IF(AND(A10<>"",COUNTIFS($A$1:A10,A10,$B$1:B10,B10)=COUNTIFS(A:A,A10,B:B,B10)),SUMIFS($D$1:D10,$A$1:A10,A10,$B$1:B10,B10,$C$1:C10,"<>DISC",$C$1:C10,"<>REBT"),"")
F11	=IF(AND(A11<>"",COUNTIFS($A$1:A11,A11,$B$1:B11,B11)=COUNTIFS(A:A,A11,B:B,B11)),SUMIFS($D$1:D11,$A$1:A11,A11,$B$1:B11,B11,$C$1:C11,"<>DISC",$C$1:C11,"<>REBT"),"")
F12	=IF(AND(A12<>"",COUNTIFS($A$1:A12,A12,$B$1:B12,B12)=COUNTIFS(A:A,A12,B:B,B12)),SUMIFS($D$1:D12,$A$1:A12,A12,$B$1:B12,B12,$C$1:C12,"<>DISC",$C$1:C12,"<>REBT"),"")
F13	=IF(AND(A13<>"",COUNTIFS($A$1:A13,A13,$B$1:B13,B13)=COUNTIFS(A:A,A13,B:B,B13)),SUMIFS($D$1:D13,$A$1:A13,A13,$B$1:B13,B13,$C$1:C13,"<>DISC",$C$1:C13,"<>REBT"),"")

 

[Eric W]

Another option for F1:

=IF(B1=B2,"",SUM(SUMIFS(D:D,A:A,{"A001","A002"},B:B,B1))-SUM(SUMIFS(D:D,A:A,{"A001","A002"},B:B,B1,C:C,{"DISC";"REBT"})))

 

[Joyner]

Another option for F1:

=IF(B1=B2,"",SUM(SUMIFS(D:D,A:A,{"A001","A002"},B:B,B1))-SUM(SUMIFS(D:D,A:A,{"A001","A002"},B:B,B1,C:C,{"DISC";"REBT"})))

Hello Mr. E.W.

I see you used a semi-colon for the last criteria argument and I see that used in some cases instead of the comma, is there someplace where it is explained why/when? Thanks

 

[Eric W]

@Joyner, the short answer is that you can use 2 array constants in SUMIFS, one must be a horizontal array, which is separated by commas, and one must be a vertical array, which is separated by semicolons.

The long answer has to do with how Excel handles arrays internally. If you're really interested, or if you're having trouble sleeping, I wrote up a longer explanation in post # 8 here:

https://www.mrexcel.com/forum/excel-questions/1020655-countifs-multiply-criteria.html

 

[Joyner]

The long answer has to do with how Excel handles arrays internally. If you're really interested, or if you're having trouble sleeping, I wrote up a longer explanation in post # 8 here:

https://www.mrexcel.com/forum/excel-questions/1020655-countifs-multiply-criteria.html

Thank you