Countif value is not blank and less than 70% of adjacent cell"s value

[jfin1ty]

Hi all,
Hoping someone can help me with this. I'm guessing it super simple, but I can't find a solution anywhere online.
Basically, what I'm trying to do is count the number of times each cell in a range (G4:G9) is not blank and is less than 70% of the value of the cell that is 2 columns adjacent from is in range E4:E10.
I'd likely put the formula in cell A1. I know I could easily use a helper column like I have on the attached sheet and just count the true or false values but space on the table where I plan on actually using the formula is very limited.

Book1
	A	B	C	D	E	F	G	H
1
2
3
4					44		30	True
5					44		40	False
6					40		27	True
7					25		24	False
8					12		5	True
9					12		11	False
Sheet1

Cell Formulas
Range	Formula
H4:H9	H4	=IF(G4<E4-E4/100*30,"True","False")

Thanks in advance,
Jfinity.

 

[etaf]

does this work
=SUMPRODUCT(--(G4:G9<(E4:E9*0.7))*(G4:G9<>""))

Book16
	A	B	C	D	E	F	G	H	I
1	3
2
3
4					44		30	True	TRUE
5					44		40	False	FALSE
6					40		27	True	TRUE
7					25			True	TRUE
8					12		5	True	TRUE
9					12		11	False	FALSE
Sheet1

Cell Formulas
Range	Formula
A1	A1	=SUMPRODUCT(--(G4:G9<(E4:E9*0.7))*(G4:G9<>""))
H4:H9	H4	=IF(G4<E4-E4/100*30,"True","False")
I4:I9	I4	=G4<(E4*0.7)

 

[jfin1ty]

does this work
=SUMPRODUCT(--(G4:G9<(E4:E9*0.7))*(G4:G9<>""))

Book16
	A	B	C	D	E	F	G	H	I
1	3
2
3
4					44		30	True	TRUE
5					44		40	False	FALSE
6					40		27	True	TRUE
7					25			True	TRUE
8					12		5	True	TRUE
9					12		11	False	FALSE
Sheet1

Cell Formulas
Range	Formula
A1	A1	=SUMPRODUCT(--(G4:G9<(E4:E9*0.7))*(G4:G9<>""))
H4:H9	H4	=IF(G4<E4-E4/100*30,"True","False")
I4:I9	I4	=G4<(E4*0.7)

Hi etaf,
If I use your formula on its own in a separate sheet it works perfectly however when I put it in the workbook I'm making I get the "#VALUE" error.
Is your formula affected by the fact my actual data in G4:G9 and E4:E9 isn't numerical values but rather formulas evaluating to numbers?

For example, on the actual sheet that I'm going to use the code for, the "number" in E4 is the result of a vlookup based on data entered in C4 that looks up a table on another sheet within the workbook and looks like this =IFERROR(VALUE(VLOOKUP(C4,Trucks[#All],3,FALSE)+F4),"")
And the "number" in G4 is the result of a very complicated IF/SUMIF formula that looks like this
=IFERROR(IF(OR(H4<>"",D4<>""),IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="T")),SUM(O4:INDEX(O4:$O$100,MATCH(TRUE,($O4:O$100=""),0)))-SUMIFS(O4:O$100,AC4:AC$100,"NO",L4:L$100,"<>RTW",L4:L$100,"<>RTB")-SUM(SUMIF(L4:L$100,{"RTW","RTB"},O4:O$100))),""),"")

 

[Peter_SSs]

Give this one a try.

23 05 22.xlsm
	A				E		G
1	3
2
3
4					44		30
5					44		40
6					40		27
7					25		24
8					12		5
9					12		11
Count (2)

Cell Formulas
Range	Formula
A1	A1	=COUNT(FILTER(G4:G9,IFERROR(G4:G9-0.7*E4:E9<0,0),""))

 

[jfin1ty]

Give this one a try.

23 05 22.xlsm
	A				E		G
1	3
2
3
4					44		30
5					44		40
6					40		27
7					25		24
8					12		5
9					12		11
Count (2)

Cell Formulas
Range	Formula
A1	A1	=COUNT(FILTER(G4:G9,IFERROR(G4:G9-0.7*E4:E9<0,0),""))

Legend! That's done the trick. Thanks so much.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[jfin1ty]

You're welcome. Thanks for the follow-up.

Hi Peter,
If I wanted to add a few more criterion to this formula how would I do it?
I'd like to add criteria for it to only count where values in the range L4:L9 = "WP" and where values in the range AG4:AG9 are blank.
I've not used the filter formula before so im struggling to work out how to add to it.

 

[jfin1ty]

Hi Peter,
If I wanted to add a few more criterion to this formula how would I do it?
I'd like to add criteria for it to only count where values in the range L4:L9 = "WP" and where values in the range AG4:AG9 are blank.
I've not used the filter formula before so im struggling to work out how to add to it.

All good Peter. I worked it out in the end. It was a matter of adding in some more brackets and "*"

 

[Peter_SSs]

All good Peter. I worked it out in the end.

Great, thanks for letting us know.