SUMIFS Leading Zeros & Dual Conditional Statement

[genheals]

Hello,

I have an issue with a SUMIFS formula that I am using in a spreadsheet. The idea is to take a column of data called "Reference Number", paste this column into a new column and make it unique, using the Excel data duplication removal feature. I then want to take a conditional summation for each UNIQUE reference number. These are account numbers, so the leading zeros are relevant and must be preserved. I also want to have a condition that any Debit Amount less than or equal to 2 is not included in the conditional summation for each reference number.

The problem is that SUMIFS does not recognize leading zeros, and is instead ignoring these zeros and generating an inaccurate summation. Solutions online are suggesting SUMPRODUCT, but I am not sure how to have dual IF statements in that formula.

I have insert a section of the spreadsheet below for more clarity.

Thank you for any help!




SOURCE DATA TABLE

Reference Number	Debit Amount	Credit Amount
001	2700.00	0.00
01	2769.23	0.00
1	1192.31	0.00
002	1192.31	0.00
02	2.00	0.00
2	1050.00	0.00
003	1050.00	0.00
03	0.00	1923.08
3	0.98	0.00
3	1.00	0.00
3	1.00	0.00
2	961.54	0.00
2	961.54	0.00
1	1230.77	0.00

<tbody>
</tbody>

CONDITIONAL SUMMATION TABLE (INCORRECT - NO FORMULAS)

Reference Number	Debit	Credit	Total
001	7892.31	0	7892.31
01	7892.31	0	7892.31
1	7892.31	0	7892.31
002	4165.39	0	4165.39
02	4165.39	0	4165.39
2	4165.39	0	4165.39
003	1050	1923.08	-873.08
03	1050	1923.08	-873.08
3	1050	1923.08	-873.08

<tbody>
</tbody>

CONDITIONAL SUMMATION TABLE (INCORRECT - FORMULAS)

Reference Number	Debit	Credit	Total
001	=SUMIFS($B:$B,$A:$A,E2,$B:$B,">2")	=SUMIFS($C:$C,$A:$A,E2)	=F2-G2
01	=SUMIFS($B:$B,$A:$A,E3,$B:$B,">2")	=SUMIFS($C:$C,$A:$A,E3)	=F3-G3
1	=SUMIFS($B:$B,$A:$A,E4,$B:$B,">2")	=SUMIFS($C:$C,$A:$A,E4)	=F4-G4
002	=SUMIFS($B:$B,$A:$A,E5,$B:$B,">2")	=SUMIFS($C:$C,$A:$A,E5)	=F5-G5
02	=SUMIFS($B:$B,$A:$A,E6,$B:$B,">2")	=SUMIFS($C:$C,$A:$A,E6)	=F6-G6
2	=SUMIFS($B:$B,$A:$A,E7,$B:$B,">2")	=SUMIFS($C:$C,$A:$A,E7)	=F7-G7
003	=SUMIFS($B:$B,$A:$A,E8,$B:$B,">2")	=SUMIFS($C:$C,$A:$A,E8)	=F8-G8
03	=SUMIFS($B:$B,$A:$A,E9,$B:$B,">2")	=SUMIFS($C:$C,$A:$A,E9)	=F9-G9
3	=SUMIFS($B:$B,$A:$A,E10,$B:$B,">2")	=SUMIFS($C:$C,$A:$A,E10)	=F10-G10

<tbody>
</tbody>

 

[Dave Patton]

=sumproduct(--(a2:a20=e2),--(b2:b20>2),(b2:b20))

 

[genheals]

Beautiful. Thank you Dave!