COUNT blank cells within a range determined by Todays date

[stephboucher]

Please see attached document.

In Column Q, i would like to count the number of consecutive cells which contained a value - the cell range is determined by todays date and works backwards.

Thanks in advance for your help with this.

Title should say Non Blank - ie. count consecutive cells containing a value

[cbatrody]

Please use the following formula in Q4:

COUNT(C4:P4)

[stephboucher]

thanks for your help but that doesn't consider todays date or consecutive cells.

[XOR LX]

Hi,

So using today's date, that would mean we begin with a blank cell for Item B - can you confirm what your desired result is?

Regards

[stephboucher]

in the example i posted .....

Cell Q4 would display a result of 7 because .....

1st criteria is that working from p3 to c3, find the date matching today (in this case, P3 does)

therefore, working back from P3 in this example, count the number of consecutive cells which contain data.

P3 = Yes, O3 = Yes, n3 = Yes, M3 = Yes, L3 = Yes, K3 = Yes & J3 = Yes

Q4 = 7

[FDibbins]

Why is H4 excluded?

=COUNTIFS(C3:Q3,"<="&TODAY(),C4:Q4,">0") = 8

[XOR LX]

(self-deleted)

[stephboucher]

since posting this, I have amended my spreadsheet to always have a rolling 14 days of data - P3 will always be today.

I am only interested in counting the number of consecutive cells that contain data working back from P.

in the example i included, i want to count consecutive cells which have data - item A, has a break on I4 (working backwards from P), so i would only count j to p

[stephboucher]

I think ive resolved it myself.

=IF(COUNT(D4:P4)=14,14,IF(COUNT(C4:P4)=13,13,IF(COUNT(E4:P4)=12,12,IF(COUNT(F4:P4)=11,11,IF(COUNT(G4:P4)=10,10,IF(COUNT(H4:P4)=9,9,IF(COUNT(I4:P4)=8,8,IF(COUNT(J4:P4)=7,7,IF(COUNT(K4:P4)=6,6,IF(COUNT(L4:P4)=5,5,IF(COUNT(M4:P4)=4,4,IF(COUNT(N4:P4)=3,3,IF(COUNT(O4:P4)=2,2,IF(COUNT(P4)=1,1,0))))))))))))))

[XOR LX]

Perhaps:

=MATCH(TRUE,INDEX(C4:P4,COLUMN(P4)-N(INDEX(COLUMN(C4:P4),,)))="",0)

Regards