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

musclebunny

New Member
Joined
Jan 5, 2023
Messages
9
Platform
  1. Web
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.

1672905032245.png
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.

1672989381006.png
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
Solution

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top