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

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>

 

[AliGW]

Here's one way:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I
1	Data 1 : Items we brought
2
3	Items	Price	Shop		Shop B	Cost	Estimate	+5%	Difference
4	chair	490	Shop A		table	492	318	333.90	Overcharged
5	table	492	Shop B		calculator	512	327	343.35	Overcharged
6	scissor	487	Shop A
7	calculator	512	Shop B
8
9	Data 2 : An estimate given by Shop B
10
11	chair	399
12	table	318
13	scissor	409
14	calculator	327
Sheet1

Cell Formulas
Range	Formula
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]

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.

 

[AliGW]

Not hard to adapt!

Excel 2016 (Windows) 32 bit

	E	F	G	H	I
3	Shop B	Cost	Estimate	+5%	Difference
4	table	492	318	333.90	Overcharged
5	calculator	512	327	343.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.

 

[AliGW]

Or try this:

Excel 2016 (Windows) 32 bit
	E	F	G
3	Shop B	Cost	Difference
4	table	158.10	Overcharged
5	calculator	168.65
Sheet1

Cell Formulas
Range	Formula
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]

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]

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]

Try this to show the amount:

Excel 2016 (Windows) 32 bit
	E	F	G
3	Shop B	Difference	Outcome
4	table	158.10	Overcharged by 326.75
5	calculator	168.65
Sheet1

Cell Formulas
Range	Formula
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]

.........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]

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