Question description:
This user has given permission to use the problem statement for this
blog.
I want to count how many rows have not had any action taken yet but are not yet Overdue. So my row G from cell G3 to G102 need to be blank and cell F3 to F102 need to be blank. I don't want it to count any rows that are blank (which naturally have the G and F columns blank.
I want to count how many cells have F and G columns blank but still have text in column B which means that there is a task in that row. I've tried a few different formulas but they won't work.
Solved by I. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
27/08/2018 - 01:12
Helloo
User
27/08/2018 - 01:12
Hi
Excelchat Expert
27/08/2018 - 01:12
would you be able to share a sample data for the issue?
User
27/08/2018 - 01:15
There's some sample data
Excelchat Expert
27/08/2018 - 01:15
Ok..
Excelchat Expert
27/08/2018 - 01:16
can you quickly brief the formula issue?
User
27/08/2018 - 01:16
I want a formula that will count rows that have an action description but don't have anything in F or G columns, so are completely unactioned.
User
27/08/2018 - 01:16
I have a second sheet that I'm trying to consolidate my data in.
Excelchat Expert
27/08/2018 - 01:17
in your description you have mentioned... you have tired few formula...
User
27/08/2018 - 01:17
Yes
Excelchat Expert
27/08/2018 - 01:17
have you looked at countifs?
User
27/08/2018 - 01:17
Yes.
Excelchat Expert
27/08/2018 - 01:18
Ok... what all "action description" will be there?
User
27/08/2018 - 01:18
But if I do count if F and G are blank it's giving me all the rows in the range, not just the ones with text in Action Description.
User
27/08/2018 - 01:18
What do you mean?
Excelchat Expert
27/08/2018 - 01:19
In column-B.. you have kept "Do This"..
User
27/08/2018 - 01:19
In my table I have 19 rows but 7 are overdue so I want a formula that will give me 12, so have an action but they're not overdue or in progress or complete.
Excelchat Expert
27/08/2018 - 01:19
what other options will be there?
User
27/08/2018 - 01:19
They all vary.
User
27/08/2018 - 01:19
For example:
User
27/08/2018 - 01:20
Provide copy of asbestos register to Andy
OR
User
27/08/2018 - 01:20
Investigate siten for zone 10/11 trolley
User
27/08/2018 - 01:20
OR
User
27/08/2018 - 01:20
Large HSR Cement Line – Assess platform when using high pressure hose
Excelchat Expert
27/08/2018 - 01:20
hmm... ok.. then you mean there is no standard set of value...
User
27/08/2018 - 01:20
No.
User
27/08/2018 - 01:20
Not at all.
Excelchat Expert
27/08/2018 - 01:22
So.. the requirement is to get a could of cells which has progress and Overdue blank, while there is an action defined.
User
27/08/2018 - 01:22
In my overdue column I have a formula that works with the dates in D to enter the text "OVERDUE" if the date is before today.
User
27/08/2018 - 01:22
Yes.
User
27/08/2018 - 01:22
F and G blank but has text in B.
Excelchat Expert
27/08/2018 - 01:22
ok.. give me few mins... let me check on the formula
Excelchat Expert
27/08/2018 - 01:23
In the sample which you have built.. what is the output value..?
Excelchat Expert
27/08/2018 - 01:24
I think it should be 6... Correct?
User
27/08/2018 - 01:24
It should be 1.
User
27/08/2018 - 01:24
There are 5 actions.
User
27/08/2018 - 01:24
4 have either progress or overdue.
User
27/08/2018 - 01:24
so 1 is what I want.
Excelchat Expert
27/08/2018 - 01:25
ohh.. ok. got it.. now this will be tricky!!!
User
27/08/2018 - 01:25
I know, that's why I need help!
Excelchat Expert
27/08/2018 - 01:25
let me figure out formula..
Excelchat Expert
27/08/2018 - 01:35
Please check the formula in cell J2
[Uploaded an Excel file]
User
27/08/2018 - 01:36
Please wait, I'll check if it works in my actual spreadsheet.
Excelchat Expert
27/08/2018 - 01:36
ok..
User
27/08/2018 - 01:41
I opened your formula and it's giving me a #VALUE! error.
Excelchat Expert
27/08/2018 - 01:41
its an array formula..
User
27/08/2018 - 01:41
What's that mean?
Excelchat Expert
27/08/2018 - 01:42
after you enter it.. press CTRL + SHIFT + ENTER
Excelchat Expert
27/08/2018 - 01:42
together to get the output
User
27/08/2018 - 01:43
It's given me 17. I want 12..
Excelchat Expert
27/08/2018 - 01:45
are there any other criteria..?? b'cos on the sample data here its working.. and I checked it on my local excel.. it was working..
User
27/08/2018 - 01:45
I tried to alter it to my data and it's given me -81. 81 is how many rows don't have a task assigned.
User
27/08/2018 - 01:45
I have this formula for my "Overdue" Column:
User
27/08/2018 - 01:45
=IF(ISBLANK(D12)," ",IF(AND(D12<TODAY(),ISBLANK(F12)),"OVERDUE"," "))
User
27/08/2018 - 01:46
They're not blank cells.
User
27/08/2018 - 01:46
They're only blank because I told them to be with a formula.
Excelchat Expert
27/08/2018 - 01:46
ohhh... ok..
Excelchat Expert
27/08/2018 - 01:46
hmmm.. interesting.. now it becomes even more challenging!!!
Excelchat Expert
27/08/2018 - 01:47
can you have the same formula built on the googlesheet here??
Excelchat Expert
27/08/2018 - 01:47
it will be easy for me to understand the context..
User
27/08/2018 - 01:48
Ok, I have but the overdue dates aren't working.
Excelchat Expert
27/08/2018 - 01:48
ok
User
27/08/2018 - 01:49
The top one should be overdue
User
27/08/2018 - 01:50
Now so should row 4.
Excelchat Expert
27/08/2018 - 01:51
column D is saved as Text..
Excelchat Expert
27/08/2018 - 01:52
so the formula output is incorrect..
User
27/08/2018 - 01:52
It should be data.
User
27/08/2018 - 01:52
*date
User
27/08/2018 - 01:52
Can you change that please.
Excelchat Expert
27/08/2018 - 01:53
updating it..
Excelchat Expert
27/08/2018 - 01:53
since there is a formula the previous formula is failing..
Excelchat Expert
27/08/2018 - 01:54
let me see how i can correct it..
User
27/08/2018 - 01:54
Ok
Excelchat Expert
27/08/2018 - 01:55
does column F has a formula?
User
27/08/2018 - 01:56
No.
Excelchat Expert
27/08/2018 - 01:56
ok
User
27/08/2018 - 01:56
I was thinking of putting a drop down data validation in there...
Excelchat Expert
27/08/2018 - 01:57
for now i will consider there is a formula based value and try to get a solution
User
27/08/2018 - 01:59
Thank you.
Excelchat Expert
27/08/2018 - 02:04
Apparently.. there is a simple solution.. ;)
Excelchat Expert
27/08/2018 - 02:04
please check the formula in cell J3
Excelchat Expert
27/08/2018 - 02:06
I have marked the session as DONE..
Excelchat Expert
27/08/2018 - 02:06
Please leave a feedback when you close the excel
User
27/08/2018 - 02:07
Ok
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.
