Excel - COUNT Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Is it possible to have a function (like a COUNTIF) that will count sells that meet specific criteria, but the range is limited to the 10 cells in the range that immediately precede a blank cell? So for example, the general range is A1:Z1, but I want the range to be specific to the last ten cells immediately prior to the first blank cell in the general range. Thank you.
Solved by O. F. in 26 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 13/07/2018 - 01:51
Greetings! Thank you for choosing Got It Pro!
Excelchat Expert 13/07/2018 - 01:51
Seems like you need help on your COUNTIF formula.
Excelchat Expert 13/07/2018 - 01:51
Is that right?
User 13/07/2018 - 01:51
yes
User 13/07/2018 - 01:52
I'm not sure that I would like to do is doable
Excelchat Expert 13/07/2018 - 01:52
Would you be able to provide a sample data for me?
Excelchat Expert 13/07/2018 - 01:52
Don't worry, we'll know soon enough.
Excelchat Expert 13/07/2018 - 01:52
So yeah, would you be able to share your sheet or at least make a sample data on our workbook here?
User 13/07/2018 - 01:52
sure, let me try
Excelchat Expert 13/07/2018 - 01:53
User 13/07/2018 - 01:53
So I have stats like this
User 13/07/2018 - 01:53
then I have functions
User 13/07/2018 - 01:54
let me try to get you the function
Excelchat Expert 13/07/2018 - 01:54
Sure.
User 13/07/2018 - 01:54
So that function (it's in another worksheet) counts according to specific criteria to give me the record of the team
Excelchat Expert 13/07/2018 - 01:55
Okay, hold on let me see.
User 13/07/2018 - 01:55
however, what I want to do is to use the same criteria, but add a further limitation: that only the 10 blank cells (of row 5) immediately prior to a blank cell be counted
User 13/07/2018 - 01:56
in my example, there are no blank cells, as the season is over. However, next season, I will be filling out the cells as the games are played
Excelchat Expert 13/07/2018 - 01:56
Can we edit our data here so it will show like it's the next season already?
User 13/07/2018 - 01:56
yes, let me do that
User 13/07/2018 - 01:57
So right now there is only data up to no. 13 (in row 4)
User 13/07/2018 - 01:58
I would like the COUNTIF function to apply only to no. 4 to no. 13
User 13/07/2018 - 01:58
(as no. 14 is blank in row 5)
Excelchat Expert 13/07/2018 - 01:58
Ah, it's Row 5. Hold on.
Excelchat Expert 13/07/2018 - 01:58
Wasn't able to see since it's filled in black :)
Excelchat Expert 13/07/2018 - 01:59
There we go.
Excelchat Expert 13/07/2018 - 01:59
Alright, now, what do we want to count again?
User 13/07/2018 - 01:59
so row 5 represents a win (!) or a loss (x)
User 13/07/2018 - 01:59
the way the COUNTIF is structure is that is gives me a result of x-x-x
Excelchat Expert 13/07/2018 - 01:59
yes.
User 13/07/2018 - 01:59
win-loss-overtime loss
Excelchat Expert 13/07/2018 - 01:59
Okay. May I see your formula?
User 13/07/2018 - 02:00
R25
User 13/07/2018 - 02:00
I pasted it
User 13/07/2018 - 02:00
but the values are off
Excelchat Expert 13/07/2018 - 02:00
Oohh..
User 13/07/2018 - 02:00
because they are coordinated with me other sheet
Excelchat Expert 13/07/2018 - 02:00
That's fine.
Excelchat Expert 13/07/2018 - 02:00
So, does this formula consider your blanks?
User 13/07/2018 - 02:01
the blanks are currently in the range, but they do not return a result, as there is no data
Excelchat Expert 13/07/2018 - 02:01
Looking at this sample we have now. Your formula should show 1-12-0 , right?
User 13/07/2018 - 02:01
1-11-1
User 13/07/2018 - 02:02
because one loss was in OT
User 13/07/2018 - 02:02
(row 20)
Excelchat Expert 13/07/2018 - 02:02
Ah yes. Okay.
Excelchat Expert 13/07/2018 - 02:02
So, is your current formula correct? What is it showing?
User 13/07/2018 - 02:03
my current formula is correct, but it pertains to the whole range, which is not being shown here
User 13/07/2018 - 02:03
I pasted the formula here, but the numbers are off - they coincide with my other worksheet
User 13/07/2018 - 02:03
complicated!
Excelchat Expert 13/07/2018 - 02:04
Haha. It's fine. Let's do it this way.
Excelchat Expert 13/07/2018 - 02:04
Can you give me an example on when your formula is going to be incorrect?
Excelchat Expert 13/07/2018 - 02:05
Or are we not trying to change your formula?
User 13/07/2018 - 02:05
It would never be incorrect. The criteria would always remain the same - i.e., whether there is an "x" or a "!" in row 5, whether there is an "E" or "W" in row 10, whether there is text in row 20
User 13/07/2018 - 02:05
and it would always be for the same general range
User 13/07/2018 - 02:06
however, I want to tailor it to apply only to the 10 cells that immediately precede a blank cell in row 5
User 13/07/2018 - 02:06
User 13/07/2018 - 02:07
every time a cell is populated in row 5, the specific range would move over to the right - i.e., the 10 cells would move one cell over to the right in row 5
Excelchat Expert 13/07/2018 - 02:07
So... you want your range selection to be only those that are with values, excluding those that are blank.
Excelchat Expert 13/07/2018 - 02:08
But, if a value is added to a blank cell, it should consider that new value as well.
Excelchat Expert 13/07/2018 - 02:08
Did I get that right?
User 13/07/2018 - 02:08
yes.
User 13/07/2018 - 02:08
the specific range would be limited to 10 cells at any given time
Excelchat Expert 13/07/2018 - 02:08
It's always just 10 cells?
User 13/07/2018 - 02:08
those ten cells must be the 10 cells immediately preceding the first blank cell in row 5
User 13/07/2018 - 02:08
yes
Excelchat Expert 13/07/2018 - 02:08
the 10 cells after the first blank...
User 13/07/2018 - 02:08
but there is a general range, given that the specific range of 10 cells moves every time a cell is populated in row 5
Excelchat Expert 13/07/2018 - 02:08
So in this case...
User 13/07/2018 - 02:09
before the first blank cell
Excelchat Expert 13/07/2018 - 02:09
Since column R is blank. the formula should take the 10 cells after column R?
Excelchat Expert 13/07/2018 - 02:09
Given they're not blank for example?
User 13/07/2018 - 02:09
before column R
User 13/07/2018 - 02:09
right, because they are not blank
User 13/07/2018 - 02:09
so, Q, P, O, N...
Excelchat Expert 13/07/2018 - 02:10
Hold on. I'm confused. Is it the 10 cells AFTER the first blank, or the 10 cells BEFORE the first blank?
User 13/07/2018 - 02:10
before
Excelchat Expert 13/07/2018 - 02:10
Okoay, before.
Excelchat Expert 13/07/2018 - 02:10
Hold on, let me see what I can do
Excelchat Expert 13/07/2018 - 02:10
Excelchat Expert 13/07/2018 - 02:11
You didn't extend the session? I don't think I'd be able to talk to you.
Excelchat Expert 13/07/2018 - 02:11
Or at least you won't be able to talk to me.
Excelchat Expert 13/07/2018 - 02:11
But okay hold on.
Excelchat Expert 13/07/2018 - 02:15
Okay, so the requirement of letting the formula automatically move itself is not possible. We could probably make another formula that would cater to what you want but the requirement is not really clear.
Excelchat Expert 13/07/2018 - 02:16
With the information I have, I think your best bet is moving your range selection manually.
Excelchat Expert 13/07/2018 - 02:16
I hope this helps. In any case, thank you for choosing Got It Pro! Feel free to come back for more of your excel help needs!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.