Finding Count From Last Few Cells in Array

[raheelk]

Friends,

I have one data as shown in attachment. I need excel formula to count values with cell > 1.75 from last 7 columns (i.e from Col I to O) only.

Please help to build one formula for me

[Olly]

Try:

Please Login or Register  to view this content.

[raheelk]

Dear olly,

I am sorry was not able to explain my query properly.

I want count of last specified cells in an array of range of a certain value.

In our example i want to count where values >1.75 appeared from array specified as A to O in last few specified cells (i.e I to O)

Please support

[Olly]

Nope, still not understanding what you want. Can you attach a workbook showing what you have, and what your desired outcome is.

I'm not clear how you are specifying your total array (A:O), your required array (I:O) and your threshold value (1.75).

[raheelk]

Dear Olly,

I have attached a worksheet.

If you see the sheet, we have DATES ranging from 14 Nov to 27 Nov. Now as I have applied countif in COL B giving array range from D2:Q2 with criteria >1.75, the result shown is 5 because there are 5 values from 14 Nov to 27 Nov with >1.75. right!

I actually want to make a formula in COL C which should have array range from D2:Q2 and by giving it 7, it should mean that it will consider only last seven days 21 Nov to 27 Nov from this array range of D2:Q2.

So if i do this manually, output should now be 3 as values >1.75 are present at 21 Nov, 23 Nov and 25 Nov in last seven days in array D2:Q2.

[Olly]

So , something like this:

Put your value of 7 in cell C1.

In C2, try:

Please Login or Register  to view this content.

[raheelk]

Lots of Thanks ... It did the trick !