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
Go ahead.
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
so it would adapt as text is added to row 5
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
Please extend the session
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.
