Non-Contiguous COUNTIF range

[howesmr]

Hello All,
I am struggling to find a way to COUNTIF the rows label 'Qty' are less than a value. I have tried naming the range but COUNTIF doesn't seem to work on a named range. Ultimately I would like formulas to for Qty < 30, Qty < 40, etc. I would do the same with time (formatted in m:ss) The whole sheet is quite massive.

The only way I have been successful is to duplicate the qty values in helper cells way off to the right then do a COUNTIF on that range, and again with the Times. But size of the data and the different variables I need to do this it is becoming quite massive with 100,000s extra albeit simple formulas where it could be 10 COUNTIF formulas for each variable.

I so far have tried the named non-contiguous range to no avail but assume there is a clever way to do a countif on the whole array but only when the left most value ="qty"

	1	2	3	4
Time	7:24	5:18	8:32	7:12
Qty	23	43	14	95
Time	6:57	7:43	7:33	5:46
Qty	32	21	17	53
Time	8:11	7:32	6:42	6:03
Qty	91	24	76	67

<tbody>
</tbody>

 

[jorismoerings]

Hi and welcome to MrExcel,

see if this helps:

Book1
	A	B	C	D	E	F	G	H
1	Time	07:24	05:18	08:32	07:12
2	Qty	23	43	14	95
3	Time	06:57	07:43	07:33	05:46
4	Qty	32	21	17	53		<30	5
5	Time	08:11	07:32	06:42	06:03		<40	6
6	Qty	91	24	76	67
Sheet1

Cell Formulas
Range	Formula
H4	=SUMPRODUCT(($A$1:$A$6="Qty")*($B$1:$E$6<30))

 

[howesmr]

jorismoerings That is exactly what I needed. This will allow me to remove over 100 helper columns with over 10,000 rows. Thanks so much