I'm having difficulty with counting non blank cells in a long row, between two selected values.

musclebunny · Jan 5, 2023

I have a long row of numbers which is looking at 2 months of daily numbers data. (Approx 50 columns wide).
Between the two months of numbers are some blank cells to seperate the months. The blank cells are required as months have varying numbers of days.
Referring to the pic below, in cell A5 I have a value which is the HHV for the 1st month. (Highest High Value or max value).
In cell A6 is the HHV for the 2nd month.

What is the formula which includes identifying cells A5 and A6, counts how many days of full cells between the two HHV's?
I've had a go with COUNTIF and COUNTIFS but not having any luck.
I'm using numerous rows of changing data so I don't wish to be manually entering any figures into the formula once the formula is in place.

I'm using googlesheets in this instance but from what I can gather Sheets is using the same format on Countif formulas.

Peter_SSs · Jan 5, 2023

musclebunny said:
I'm using googlesheets

.. so I have moved this to the forum for Sheets questions

musclebunny · Jan 6, 2023

Another approach I could use instead of counting cells, how do I identify the date with a particular adjacent value?
In this example cell B4 = 1.82.
Which formula can tell the date of that value?
Either a cell value "G1" or "12 Dec 2022" will suffice as a result.

bebo021999 · Jan 6, 2023

Use INDEX-MATCH

Excel Formula:

=INDEX($A$1:$P$1,MATCH($B$4,$A$2:$P$2,0))

Peter_SSs · Jan 6, 2023

What if more than one cell in row 2 contains that value (eg 1.93)?

musclebunny · Jan 6, 2023

bebo021999 said:
Use INDEX-MATCH

Excel Formula:

=INDEX($A$1:$P$1,MATCH($B$4,$A$2:$P$2,0))

WOW! That hit the nail on the head, spot on target!. Thanks bebo. Very good!

musclebunny · Jan 6, 2023

Peter_SSs said:
What if more than one cell in row 2 contains that value (eg 1.93)?

In this instance it is very rare to get the same value in the same month, it may happen but if it does, the two different dates will not have a major effect on what I'm attempting to achieve.
The algo I'm constructing is not an exact science, but the outcome of the formula bebo kindly supplied is far superior to my earlier efforts.
Thanks guys for your assistances.

Peter_SSs · Jan 7, 2023

musclebunny said:
In this instance it is very rare to get the same value in the same month, it may happen but if it does, the two different dates will not have a major effect on what I'm attempting to achieve.

Thanks for the additional information. I think that you could get the same result with a single function.

Excel Formula:

=MINIFS(A$1:P$1,A2:P2,B4)

musclebunny · Jan 8, 2023

Peter_SSs said:
Thanks for the additional information. I think that you could get the same result with a single function.

Excel Formula:

=MINIFS(A$1:P$1,A2:P2,B4)

Hi Peter, yes, your formula suggestion also works a treat!

Peter_SSs · Jan 8, 2023

Cheers.

I'm having difficulty with counting non blank cells in a long row, between two selected values.

musclebunny

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

musclebunny

New Member

bebo021999

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

musclebunny

New Member

musclebunny

New Member

Peter_SSs

MrExcel MVP, Moderator

musclebunny

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

I'm having difficulty with counting non blank cells in a long row, between two selected values.

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

Well-known Member

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock