Unsure how to add additional vlookup into sumifs formula

[SMExcel]

Hi All,

I am currently calculating the adjustment for change in % for value of property based on postcode and whether its a house or unit. The formula I currently have for the values in Adjustment column below are:
=D3*SUMIFS($E$12:$E$17,$B$12:$B$17,B3,$C$12:$C$17,C3)
=D4*SUMIFS($E$12:$E$17,$B$12:$B$17,B4,$C$12:$C$17,C4)
=D5*SUMIFS($E$12:$E$17,$B$12:$B$17,B5,$C$12:$C$17,C5)

I am now trying to integrate an additional check in place so that there must be > 10 sales in order for the change in % to apply. For example, E3 should be 0, as there were only 8 sales for a house for post code 3500.

My thought process is that an additional vlookup is required to search for the type and postcode against whether there were > 10 sales, but i'm not sure how this can be integrated into the current formula.

Any comments would be greatly appreciated.

Property Name	Type	Postcode	Cost Base	Adjustment
Property 1	House	3500​	200000​	7000​
Property 2	Unit	3400​	300000​	3750​
Property 3	House	3300​	400000​	32000​
Data - Increase in Market Value
	Type	Postcode	# of Sales	Change in %
	House	3500​	8​	3.50%​
	Unit	3500​	15​	2.00%​
	House	3400​	12​	4.00%​
	Unit	3400​	55​	1.25%​
	House	3300​	30​	8.00%​
	Unit	3300​	6​	1.00%​

 

[Dave87]

in cell A12 type =B12&"-"&C12
copy down to bottom of lookup data
in cell E3 type =IF(VLOOKUP(B3&"-"&C3,$A$11:$D$17,4,FALSE)<10,0,D3*SUMIFS($E$12:$E$17,$B$12:$B$17,B3,$C$12:$C$17,C3))

 

[SMExcel]

Thank you Dave87 that worked for me.

 

[Peter_SSs]

i'm not sure how this can be integrated into the current formula.

You could have done so like this (without the helper column).

SMExcel 2020-07-01 1.xlsm
	A	B	C	D	E
1	Property Name	Type	Postcode	Cost Base	Adjustment
2
3	Property 1	House	3500	200000	0
4	Property 2	Unit	3400	300000	3750
5	Property 3	House	3300	400000	32000
6
7
8	Data - Increase in Market Value
9
10		Type	Postcode	# of Sales	Change in %
11
12		House	3500	8	3.50%
13		Unit	3500	15	2.00%
14		House	3400	12	4.00%
15		Unit	3400	55	1.25%
16		House	3300	30	8.00%
17		Unit	3300	6	1.00%
Sheet3

Cell Formulas
Range	Formula
E3:E5	E3	=D3*SUMIFS($E$12:$E$17,$B$12:$B$17,B3,$C$12:$C$17,C3)*(SUMIFS($D$12:$D$17,$B$12:$B$17,B3,$C$12:$C$17,C3)>10)