COUNTIF using HLOOKUP as start range and 'last blank cell in row' as end range (maybe)?? Help Please

[77highland]

Hi All,

Need help with 2 formulas, kind of related as both need a HLOOKUP start range and 'last blank cell in row' as end range (i believe), any help will be greatly appreciated.......


In column M (M2:M5) i need a formula along the lines of......

=IF(L2="Low",COUNTIF(HLOOKUP(K2,A1:J5,2):'last blank cell in row',"<600"),IF(L2="Low",COUNTIF(HLOOKUP(K2,A1:J5,2):'last blank cell in row',">600"),IF(L2="","No")))

In column O (O2:O5) i need a formula along the lines of......

=AVERAGE(HLOOKUP(N2,A1:J5,2):'last blank cell in row')

But please note that the date range continuously expands via inserting columns (between columns J and K in the example below).

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	01/05/18	02/05/18	03/05/18	04/05/18	05/05/18	06/05/18	07/05/18	08/05/18	09/05/18	10/05/18	Priority Tracking Start Date	Priority	Priority % Hit Rate	OH Completion Date	Average Mileage since OH
2	300	400	500	500	700	200	400	500			01/05/18			02/05/18
3	400	200	800	900	500	600	1000	400			02/05/18	High		01/05/18
4	200	300	700	800	700	500	100	0			04/05/18	Low		03/05/18
5	400	800	1000	200	0	0	300	400			04/05/18	High		02/05/18

<tbody>
</tbody>

 

[Eric W]

Excel will automatically adjust the ranges in formulas as rows/columns are added. So the best recommendation is to add a new empty column now, which will be K, shifting K:O over one. Then you can have this layout:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1/5/2018	2/5/2018	3/5/2018	4/5/2018	5/5/2018	6/5/2018	7/5/2018	8/5/2018	9/5/2018	10/5/2018		Priority Tracking Start Date	Priority	Priority % Hit Rate	OH Completion Date	Average Mileage since OH
2	300	400	500	500	700	200	400	500					No	2/5/2018	457.1428571
3	400	200	800	900	500	600	1000	400				2/5/2018	High	4	1/5/2018	600
4	200	300	700	800	700	500	100	0				4/5/2018	Low	3	3/5/2018	466.6666667
5	400	800	1000	200	0	0	300	400				4/5/2018	High	0	2/5/2018	385.7142857
6														No		#DIV/0!

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula N2 =IF(M2="","No",COUNTIFS(A$1:K$1,">="&L2,A2:K2,IF(M2="Low","<600",">=600"))) P2 =AVERAGEIF(A$1:K$1,">="&O2,A2:K2) <tbody> </tbody>

<tbody>
</tbody>

When you add a new column, add it between J:K, and all the formulas will automatically adjust.

 

[77highland]

Hi Eric W,

Thank you very much for the reply.....

Your suggestion kind of works, but I am having some issues.......

Using your formulae and health checking a small amount of sample data some of the values returned are +1, and then if I removed the "=" from ">=" then some of the values returned are "-1".

I have been racking my brains for hours and cannot figure out whats going on

 

[Eric W]

Can you show an example of the problem, with the formulas? I can't picture how either one of those formulas could return a -1.

 

[77highland]

I apologise, scrap my last............

Someone had added a date out of sync which threw everything off. Now works like a charm.

Thank you very much Eric W

SOLVED

 

[Eric W]

Glad you got it sussed!