# Subtracting as per the criteria

#### snjpverma

##### Well-known Member
Data 1 is the details of items brought from shops A and B.
Data 2 is a menu of items in Shop B which is a long menu.
I want a formula to tell me if the items which we brought from Shop B in Data 1 is charged by more than 5% than the menu (estiamated) amount given in Data 2

Output expected as below :
If overcharged then : amount by how much it is overcharged.
If not overcharged then : "Not overcharged".

In layman way, I would manually calculate it like this: (492+512)-((318+327)*105%). The return value in this case is 326.75.

Please let me know if any additional information is needed. Highly obliged to this website.

Data 1 : Items we brought

 Items Price Shop chair 490 Shop A table 492 Shop B scissor 487 Shop A calculator 512 Shop B

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Data 2 : An estimate given by Shop B

 chair 399 table 318 scissor 409 calculator 327

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### AliGW

##### Banned
Here's one way:

Excel 2016 (Windows) 32 bit
ABCDEFGHI
1Data 1 : Items we brought
2
3ItemsPriceShopShop BCostEstimate+5%Difference
4chair490Shop Atable492318333.90Overcharged
5table492Shop Bcalculator512327343.35Overcharged
6scissor487Shop A
7calculator512Shop B
8
9Data 2 : An estimate given by Shop B
10
11chair399
12table318
13scissor409
14calculator327
Sheet1
Cell Formulas
RangeFormula
F4=VLOOKUP(E4,\$A\$4:\$B\$7,2,0)
G4=VLOOKUP(E4,\$A\$11:\$B\$14,2,0)
H4=G4*1.05
I4=IF(F4>H4,"Overcharged","Not Overcharged")
E4{=IFERROR(INDEX(\$A\$1:\$A\$7,SMALL(IF(\$C\$4:\$C\$7=\$E\$3,ROW(\$A\$4:\$A\$7)),ROWS(\$A\$3:A3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### snjpverma

##### Well-known Member
Thanks AliGW for your support. Knowing that I will have to add so many helper columns, I am a bit petrified .
BTW, this is not what i was actually wanted. As shown below. i want the totals to be subtracted and not the individual items.
Just to make it clear, please see below in blue.
=(Sum of Shop B items brought) - ((sum of those Items in the estimate)*105%)

As posted in my original post:
In layman way, I would manually calculate it like this: (492+512)-((318+327)*105%). The return value in this case is 326.75.

Last edited:

#### AliGW

##### Banned

Excel 2016 (Windows) 32 bit
EFGHI
3Shop BCostEstimate+5%Difference
4table492318333.90Overcharged
5calculator512327343.35

<tbody>
</tbody>

<tbody>
</tbody>
I4 =IF(SUM(F:F)>SUM(H:H),"Overcharged","Not Overcharged")

You can easily hide columns E, F and G.

Last edited:

#### AliGW

##### Banned
Or try this:

Excel 2016 (Windows) 32 bit
EFG
3Shop BCostDifference
4table158.10Overcharged
5calculator168.65
Sheet1
Cell Formulas
RangeFormula
F4=VLOOKUP(E4,\$A\$4:\$B\$7,2,0)-(VLOOKUP(E4,\$A\$11:\$B\$14,2,0)*1.05)
F5=VLOOKUP(E5,\$A\$4:\$B\$7,2,0)-(VLOOKUP(E5,\$A\$11:\$B\$14,2,0)*1.05)
G4=IF(SUM(F:F)>0,"Overcharged","Not Overcharged")
E4{=IFERROR(INDEX(\$A\$1:\$A\$7,SMALL(IF(\$C\$4:\$C\$7=\$E\$3,ROW(\$A\$4:\$A\$7)),ROWS(\$A\$3:A3))),"")}
E5{=IFERROR(INDEX(\$A\$1:\$A\$7,SMALL(IF(\$C\$4:\$C\$7=\$E\$3,ROW(\$A\$4:\$A\$7)),ROWS(\$A\$3:A4))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### snjpverma

##### Well-known Member
ALIGW thanks a lot. It did work fine this time.
I entered this and I am getting amount when it is overcharged.
I4 =IF(SUM(F:F)>SUM(H:H),SUM(F:F)-SUM(H:H),"Not Overcharged")

Please suggest if there is any alternate way of doing this directly without the helper columns/table.

#### AliGW

##### Banned
There may be a way using a more complex array formula, but hiding two columns is not really the end of the world (see my last suggestion)!

You'll have to wait and see what other people come up with.

#### AliGW

##### Banned
Try this to show the amount:

Excel 2016 (Windows) 32 bit
EFG
3Shop BDifferenceOutcome
4table158.10Overcharged by 326.75
5calculator168.65
Sheet1
Cell Formulas
RangeFormula
F4=VLOOKUP(E4,\$A\$4:\$B\$7,2,0)-(VLOOKUP(E4,\$A\$11:\$B\$14,2,0)*1.05)
F5=VLOOKUP(E5,\$A\$4:\$B\$7,2,0)-(VLOOKUP(E5,\$A\$11:\$B\$14,2,0)*1.05)
G4=IF(SUM(F:F)>0,"Overcharged by "&SUM(F:F),"Not Overcharged")
E4{=IFERROR(INDEX(\$A\$1:\$A\$7,SMALL(IF(\$C\$4:\$C\$7=\$E\$3,ROW(\$A\$4:\$A\$7)),ROWS(\$A\$3:A3))),"")}
E5{=IFERROR(INDEX(\$A\$1:\$A\$7,SMALL(IF(\$C\$4:\$C\$7=\$E\$3,ROW(\$A\$4:\$A\$7)),ROWS(\$A\$3:A4))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### bosco_yip

##### Well-known Member
.........As shown below. i want the totals to be subtracted and not the individual items.
Just to make it clear, please see below in blue.
=(Sum of Shop B items brought) - ((sum of those Items in the estimate)*105%)
As posted in my original post:

Maybe,

By using AliGW's layout table in post #2, enter the formula :

=SUMIFS(B4:B7,C4:C7,"Shop B")-SUMPRODUCT(ISNUMBER(MATCH(A11:A14,A4:A7,0))*(C4:C7="Shop B")*B11:B14)*1.05

The desired result is 326.75

Regards

#### snjpverma

##### Well-known Member
OMG, you are genius bosco_yip. I can't believe you did it without any helper column. Amazing job. Thank you.

Replies
0
Views
482
Replies
14
Views
1K
Replies
3
Views
66
Replies
15
Views
1K
Replies
7
Views
1K

1,191,045
Messages
5,984,326
Members
439,882
Latest member
gerdc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back