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"


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]Time[/TD]
[TD="align: center"]7:24[/TD]
[TD="align: center"]5:18[/TD]
[TD="align: center"]8:32[/TD]
[TD="align: center"]7:12[/TD]
[/TR]
[TR]
[TD="align: center"]Qty[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]95[/TD]
[/TR]
[TR]
[TD="align: center"]Time[/TD]
[TD="align: center"]6:57[/TD]
[TD="align: center"]7:43[/TD]
[TD="align: center"]7:33[/TD]
[TD="align: center"]5:46[/TD]
[/TR]
[TR]
[TD="align: center"]Qty[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]53[/TD]
[/TR]
[TR]
[TD="align: center"]Time[/TD]
[TD="align: center"]8:11[/TD]
[TD="align: center"]7:32[/TD]
[TD="align: center"]6:42[/TD]
[TD="align: center"]6:03[/TD]
[/TR]
[TR]
[TD="align: center"]Qty[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]67[/TD]
[/TR]
</tbody>[/TABLE]

 

[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