Summarise values in a column that are above a certain calculated %

[Chalklands]

Hi,
I have a table of data with these columns:

Customer Actual Sales Target Sales

I want to calculate an overall total sales value at the top of the spreadsheet for customers where Actual Sales/Target sales is above 70%.

Do I use sum product, sumif or an array formula? Any help would be greatly appreciated.

Thanks

Pete

 

[RasGhul]

Hi Pete,

Maybe you could use these?

Book1
	A	B	C	D	E	F	G	H
1	Customer	Actual	Target	Var %	CRITERIA	CRITERIA	70%
2	customer1	$60.00	$150.00	40%
3	customer2	$140.00	$150.00	93%		TOTAL	$621.00
4	customer3	$110.00	$150.00	73%
5	customer4	$70.00	$150.00	47%		CUSTOMERS	customer2	$140.00
6	customer5	$80.00	$150.00	53%			customer3	$110.00
7	customer6	$90.00	$150.00	60%			customer7	$108.00
8	customer7	$108.00	$150.00	72%			customer9	$148.00
9	customer8	$65.00	$150.00	43%			customer10	$115.00
10	customer9	$148.00	$150.00	99%
11	customer10	$115.00	$150.00	77%
Sheet1

Cell Formulas
Range	Formula
G3	=SUMIFS($B$2:$B$11,$D$2:$D$11,">="&$G$1)
G5	{=IFERROR(INDEX($A$2:$A$11,SMALL(IF($D$2:$D$11>=$G$1,ROW($D$2:$D$11)-ROW($D$2)+1),ROWS($G$5:G5))),"")}
H5	{=IFERROR(INDEX($B$2:$B$11,SMALL(IF($D$2:$D$11>=$G$1,ROW($D$2:$D$11)-ROW($D$2)+1),ROWS($G$5:G5))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Chalklands]

Thanks for your help RasGhul. I'll give it a go.