Subtracting as per the criteria

[snjpverma]

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

[TABLE="class: grid, width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Items[/TD]
[TD="width: 64, align: right"]Price[/TD]
[TD="width: 64"]Shop[/TD]
[/TR]
[TR]
[TD="width: 64"]chair[/TD]
[TD="width: 64, align: right"]490[/TD]
[TD="width: 64"]Shop A[/TD]
[/TR]
[TR]
[TD]table[/TD]
[TD="align: right"]492[/TD]
[TD]Shop B[/TD]
[/TR]
[TR]
[TD]scissor[/TD]
[TD="align: right"]487[/TD]
[TD]Shop A[/TD]
[/TR]
[TR]
[TD]calculator[/TD]
[TD="align: right"]512[/TD]
[TD]Shop B[/TD]
[/TR]
</tbody>[/TABLE]

Data 2 : An estimate given by Shop B

[TABLE="class: grid, width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]chair[/TD]
[TD="width: 64, align: right"]399[/TD]
[/TR]
[TR]
[TD]table[/TD]
[TD="align: right"]318[/TD]
[/TR]
[TR]
[TD]scissor[/TD]
[TD="align: right"]409[/TD]
[/TR]
[TR]
[TD]calculator[/TD]
[TD="align: right"]327[/TD]
[/TR]
</tbody>[/TABLE]

 

[AliGW]

There is a problem with bosco_yip's solution, however. You said:

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.

So let's assume that the menu items list for Shop B is longer than the list of items bought. Change the formula to allow a longer list and see what happens, e.g.:

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

I may be wrong, but I believe this will break the formula because the arrays are no longer of the same length.

 

[snjpverma]

AliGW, you are absolutely correct. The formula doesn't work when the DATA 2 list is longer. It only works when it is of the same length. I regret giving an example data with the same number of rows.

 

[AliGW]

This is sometimes what happens when sample data is oversimplified.

 

[bosco_yip]

There is a problem with bosco_yip's solution, however. You said:



So let's assume that the menu items list for Shop B is longer than the list of items bought. Change the formula to allow a longer list and see what happens, e.g.:

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

I may be wrong, but I believe this will break the formula because the arrays are no longer of the same length.

Then, the formula changed to :

=SUMIFS(B4:B7,C4:C7,"Shop B")-SUMPRODUCT(COUNTIFS(A4:A7,A11:A20,C4:C7,"Shop B"),B11:B20)*1.05

Regards
Bosco

 

[snjpverma]

Thanks once again Bosco, you nailed it.