Countif Question

[gmazza76]

Good afternoon all,

I think I am going a bit mad, running round in circles here.
But how do I get the sumif if section to add up if its above or equal to 90% as I think I am going mad.
I would normally just calculate against the cell but I need to do this as a sum as the cell it would reference shows like 91% (10) showing the % and the checks done as a concatenated calc.

SUM(COUNTIF(G12,">="&G3,countif(SUMIFS(Data!AE:AE,Data!Y:Y,D12)/SUMIFS(Data!AD:AD,Data!Y:Y,D12),">="&0.90)
[code/]

thanks in advance
Gavin

 

[awoohaw]

(I'm ignoring the G12,G3 count if for now)
Are you asking to sum the individual rows where the percentage of AE/AD is greater than 90%?
So, I've changed the second COUNTIF this:

=SUM(IF(Y2:Y9<>D12,0,(AE2:AE9/AD2:AD9)>0.9))

=SUM(COUNTIF(G12,">="&G3,SUM(IF(Y2:Y9<>D12,0,(AE2:AE9/AD2:AD9)>0.9)))

But, it would really help if you'd share some sample data and expected results.

 

[awoohaw]

(I'm ignoring the G12,G3 count if for now)
Are you asking to sum the individual rows where the percentage of AE/AD is greater than 90%?
So, I've changed the second COUNTIF this:

=SUM(IF(Y2:Y9<>D12,0,(AE2:AE9/AD2:AD9)>0.9))

=SUM(COUNTIF(G12,">="&G3,SUM(IF(Y2:Y9<>D12,0,(AE2:AE9/AD2:AD9)>0.9)))

But, it would really help if you'd share some sample data and expected results.

I omitted a -- in the formula.

=SUM(--IF(Y2:Y9<>D12,0,(AE2:AE9/AD2:AD9)>0.9))

=SUM(COUNTIF(G12,">="&G3,SUM(--IF(Y2:Y9<>D12,0,(AE2:AE9/AD2:AD9)>0.9)))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L													Y	Z	AA	AB	AC	AD	AE
1
2							ignored																		16					11	13
3							3																		13					11	14
4																									18					13	17
5																									14					12	17
6																									11					12	14
7																									15					12	14
8																									11					11	13
9																									15					14	14
10
11							ignored
12				11			6			2
Sheet1

Cell Formulas
Range	Formula
J12	J12	=SUM(--IF(Y2:Y9<>D12,0,(AE2:AE9/AD2:AD9)>0.9))