Nested Sumifs, to calc first instance only

[scorpionflower]

I'm struggling with the below formula in Column C. I'm trying to return only the first instance of the data in Column A while maintaining the if statement to column D.

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]GL Recon[/TD]
[TD][/TD]
[TD]'=ROUND(IF(D4="Y",SUMIFS($B$13:$B$17,$A$13:$A$17,$A4),IF(D4="N",0)),2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Invoice#[/TD]
[TD]Amount[/TD]
[TD]Taxes Accrued[/TD]
[TD]Taxable?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6415646[/TD]
[TD]456.00[/TD]
[TD]74.50[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6415646[/TD]
[TD]444.00[/TD]
[TD]74.50[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6415646[/TD]
[TD]3.00[/TD]
[TD]74.50[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5456465[/TD]
[TD]156.00[/TD]
[TD]12.87[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4564[/TD]
[TD]323.00[/TD]
[TD]35.81[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4564[/TD]
[TD]111.00[/TD]
[TD]35.81[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Taxes Accrued GL Recon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Invoice#[/TD]
[TD]Amount[/TD]
[TD]Taxes Accrued[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]6415646[/TD]
[TD]74.50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]5456465[/TD]
[TD]12.87[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]4564[/TD]
[TD]35.81[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mackers]

Hi

It looks like you want to use INDEX and MATCH in this case. Note that I'm basing my formula on your data starting in A3 and A12 (rather than A4 and A13). I've also assumed that taxable can only be Y and N, and so you only need to check once (i.e. we check if it's an "N" and then return a zero if so, otherwise we do the formula).

=ROUND(IF(D3="N",0,INDEX($B$12:$B$15, MATCH(A3,$A$12:$A$15,0))),2)

 

[scorpionflower]

Unfortunately it still results in same issue. I need for column C to only provide the result on the first instance of value in column A.

Want results such as below.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Invoice#[/TD]
[TD]Amount[/TD]
[TD]Taxes Accrued[/TD]
[TD]Taxable?[/TD]
[/TR]
[TR]
[TD="align: right"]6415646[/TD]
[TD="align: right"]456.00[/TD]
[TD="align: right"]74.50[/TD]
[TD="align: right"]y[/TD]
[/TR]
[TR]
[TD="align: right"]6415646[/TD]
[TD="align: right"]444.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]y[/TD]
[/TR]
[TR]
[TD="align: right"]6415646[/TD]
[TD="align: right"]3.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]y[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mackers]

Oh sorry, I misunderstood your requirement.

If you add something like IF(ISNUMBER(MATCH(A3,A$2:A2,0)), 0, RestOfYourFormula) and drag it down it should work

This just says "If I can find A3 in the cells above it, return a zero, otherwise do the formula".

 

[scorpionflower]

Thank you! It works as long as there's no sorting that causes the data to be separated.