Formula Help

[Arafat]

Hello Experts,

I have been working out different formulas to sum up values based on two multiple conditions and the problem being it has to add the value of the existing and the new found value. I have used sumifs but its only showing the TAX amount. please help

IRNAPM-J-50087-0415-000-1735057330004SG9999691 <tbody> </tbody>	0 <tbody> </tbody>	5286.02 <tbody> </tbody>	here I want add the GST TAX matching the COL A and COLB, i.e 5286.02+370.03 <tbody> </tbody>
IRNAPM-J-50087-0415-000-1735057330004SG9999887 <tbody> </tbody>	0 <tbody> </tbody>	868.49 <tbody> </tbody>	here I want add the GST TAX matching the COL A and COLB, i.e 868.49+60.79 <tbody> </tbody>
IRNAPM-J-50087-0415-000-1735057330004SG9999691 <tbody> </tbody>	GSTTax <tbody> </tbody>	370.03 <tbody> </tbody>
IRNAPM-J-50087-0415-000-1735057330004SG9999887 <tbody> </tbody>	GSTTax <tbody> </tbody>	60.79 <tbody> </tbody>

<tbody>
</tbody>

<tbody>
</tbody>

 

[gaz_chops]

Try

=SUMPRODUCT((RIGHT($A$1:$A$30,7)=RIGHT(A1,7))*($C$1:$C$30))

 

[zovche]

Hm I'm not exactly sure what you're looking for, but in your table above let's say we have columns A-D, C is value and D is sum with tax
Then you can use formula below in column D and you will get total with taxes =SUMIF($A$1:$A$4,A1,$C$1:$C$4)

Let me know if that's what you're looking for.

 

[Arafat]

Hi gaz_chops, Thank you your quick reply and the formula seems to work great Just want to know whether this formula will work for any length of COL A ? Because COL A will hold Invoice Numbers and they are not of fixed length.

A SALUTE and Thank you for your help

 

[Arafat]

Hi zovche,

I want to add up amount using the combination of COL A and COL B.

 

[gaz_chops]

The formula checks the last 7 digits of column A, so providing the numbers are always at the end it should work.

If you want to copy down and only show the results where there is a zero in Col B then try

=IF(B1=0,C1+SUMPRODUCT((RIGHT($A$1:$A$20,7)=RIGHT(A1,7))*($B$1:$B$20="GSTTax")*($C$1:$C$20)),"")

[/FONT][TABLE="width: 564"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]IRNAPM-J-50087-0415-000-1735057330004SG9999691[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5286.02[/TD]
[TD="align: right"]5656.05[/TD]
[/TR]
[TR]
[TD]IRNAPM-J-50087-0415-000-1735057330004SG9999887[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]868.49[/TD]
[TD="align: right"]929.28[/TD]
[/TR]
[TR]
[TD]IRNAPM-J-50087-0415-000-1735057330004SG9999691[/TD]
[TD]GSTTax[/TD]
[TD="align: right"]370.03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IRNAPM-J-50087-0415-000-1735057330004SG9999887[/TD]
[TD]GSTTax[/TD]
[TD="align: right"]60.79[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=Lucida Grande]

 

[Arafat]

Hi gaz_chops .

The formula should also match up col B , If GSTTAX is found for that particular invoice number then it should add the tax amount along with its actual value.

 

[Arafat]

Thank you, Now this is perfect. Is there a way to avoid last 7 digit comparison and instead use entire cell value for comparing ?

 

[MirakelMan]

In that case just use a SUMIFS formula like: =SUMIFS($C$1:$C$20;$A$1:$A$20;$A1) in cell E1. And copy down. If the range is bigger than 20 rows you can also just use: =SUMIFS($C:$C;$A:$A;$A1) .

 

[Arafat]

Hi MirakelMan,

Thank you for the Formula, But this is not matching COL B

this formula =IF(B1=0,C1+SUMPRODUCT((RIGHT($A$1:$A$20,7)=RIGHT(A1,7))*($B$1:$B$20="GSTTax")*($C$1:$C$20)),"") by gaz_chops is working perfect but i don't want to use last 7 digit comparison as that will appear in other invoice numbers as well so it end up adding all matching records.