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).


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

 

[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
Priority Tracking Start Date	Priority	Priority % Hit Rate	OH Completion Date	Average Mileage since OH
No
High
Low
High
No

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]2/5/2018[/TD]
[TD="align: right"]3/5/2018[/TD]
[TD="align: right"]4/5/2018[/TD]
[TD="align: right"]5/5/2018[/TD]
[TD="align: right"]6/5/2018[/TD]
[TD="align: right"]7/5/2018[/TD]
[TD="align: right"]8/5/2018[/TD]
[TD="align: right"]9/5/2018[/TD]
[TD="align: right"]10/5/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2/5/2018[/TD]
[TD="align: right"]457.1428571[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2/5/2018[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]600[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4/5/2018[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3/5/2018[/TD]
[TD="align: right"]466.6666667[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4/5/2018[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]2/5/2018[/TD]
[TD="align: right"]385.7142857[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]#DIV/0![/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]N2[/TH]
[TD="align: left"]=IF(M2="","No",COUNTIFS(A$1:K$1,">="&L2,A2:K2,IF(M2="Low","<600",">=600")))[/TD]
[/TR]
[TR]
[TH]P2[/TH]
[TD="align: left"]=AVERAGEIF(A$1:K$1,">="&O2,A2:K2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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!