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
[TABLE="width: 415"]
<tbody>[TR]
[TD]Reference Number[/TD]
[TD]Debit Amount[/TD]
[TD]Credit Amount[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD="align: right"]2700.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD="align: right"]2769.23[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]1192.31[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD="align: right"]1192.31[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]1050.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD="align: right"]1050.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1923.08[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]961.54[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]961.54[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]1230.77[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]




CONDITIONAL SUMMATION TABLE (INCORRECT - NO FORMULAS)
[TABLE="width: 393"]
<tbody>[TR]
[TD]Reference Number[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD="align: right"]7892.31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7892.31[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD="align: right"]7892.31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7892.31[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]7892.31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7892.31[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD="align: right"]4165.39[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4165.39[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD="align: right"]4165.39[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4165.39[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]4165.39[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4165.39[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD="align: right"]1050[/TD]
[TD="align: right"]1923.08[/TD]
[TD="align: right"]-873.08[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD="align: right"]1050[/TD]
[TD="align: right"]1923.08[/TD]
[TD="align: right"]-873.08[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]1050[/TD]
[TD="align: right"]1923.08[/TD]
[TD="align: right"]-873.08[/TD]
[/TR]
</tbody>[/TABLE]




CONDITIONAL SUMMATION TABLE (INCORRECT - FORMULAS)
[TABLE="width: 648"]
<tbody>[TR]
[TD]Reference Number[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]=SUMIFS($B:$B,$A:$A,E2,$B:$B,">2")[/TD]
[TD]=SUMIFS($C:$C,$A:$A,E2)[/TD]
[TD]=F2-G2[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]=SUMIFS($B:$B,$A:$A,E3,$B:$B,">2")[/TD]
[TD]=SUMIFS($C:$C,$A:$A,E3)[/TD]
[TD]=F3-G3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=SUMIFS($B:$B,$A:$A,E4,$B:$B,">2")[/TD]
[TD]=SUMIFS($C:$C,$A:$A,E4)[/TD]
[TD]=F4-G4[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]=SUMIFS($B:$B,$A:$A,E5,$B:$B,">2")[/TD]
[TD]=SUMIFS($C:$C,$A:$A,E5)[/TD]
[TD]=F5-G5[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]=SUMIFS($B:$B,$A:$A,E6,$B:$B,">2")[/TD]
[TD]=SUMIFS($C:$C,$A:$A,E6)[/TD]
[TD]=F6-G6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=SUMIFS($B:$B,$A:$A,E7,$B:$B,">2")[/TD]
[TD]=SUMIFS($C:$C,$A:$A,E7)[/TD]
[TD]=F7-G7[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]=SUMIFS($B:$B,$A:$A,E8,$B:$B,">2")[/TD]
[TD]=SUMIFS($C:$C,$A:$A,E8)[/TD]
[TD]=F8-G8[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]=SUMIFS($B:$B,$A:$A,E9,$B:$B,">2")[/TD]
[TD]=SUMIFS($C:$C,$A:$A,E9)[/TD]
[TD]=F9-G9[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=SUMIFS($B:$B,$A:$A,E10,$B:$B,">2")[/TD]
[TD]=SUMIFS($C:$C,$A:$A,E10)[/TD]
[TD]=F10-G10[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

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

 

[genheals]

Beautiful. Thank you Dave!