What am I doing wrong with this Formula =COUNTIFS({Start Date Estimated}, "", {Start Date Estimate
Hello,
I am trying to get the following formula to count the number of occurrences where the Start Date Estimated is either blank or in the past.
=COUNTIFS({Start Date Estimated}, "", {Start Date Estimated}, <TODAY()))
Can you please tell me what I am doing wrong?
Answers
-
When you write it like this you are implying AND. So your current formula is looking for cells that are blank AND in the past at the same time. What you need is an OR which would look like this...
=COUNTIFS({Start Date Estimated}, OR(@cell = "", @cell < TODAY()))
-
Thank you!!!! That worked.
Can you advise how I could pull status = blanks into this count? The formula currently excludes Complete, Cancelled, and Hold but is not pulling in blanks.
=COUNTIFS({assignee}, HAS(@cell, Metric@row), {status}, <>"Complete", {status}, <>"Cancelled", {status}, <>"Hold", {End Date Estimated}, OR(@cell = "", @cell < TODAY()))
-
What are all of the statuses that you have?
Complete
Cancelled
On Hold
blank
what else?
-
Hi Paul,
Please see below for all the Status's. I would like to include all but Complete, Hold, and Cancelled. Was hoping I could exclude the ones I didnt want in case there more added in the future.
Blanks
Need Additional Information
In Queue
Scheduled
In Progress
Complete
Hold
Cancelled
Another question (Sorry), In addition to excluding the below status & Assignee's I was looking for it to count which End Date Estimated dates were less than the Need by date. Its populating 0, which is incorrect, is there something I did wrong?
=COUNTIFS({status}, <>"Complete", {status}, <>"Cancelled", {status}, <>"Hold", {assignee}, <>"Meeting", {assignee}, <>"Schedule Milestone", {End Date Estimated}, AND(@cell < {Need by}))
-
Since you want to exclude specific statuses and account for all others (even new ones as they are added), you would want something like this...
=COUNTIFS({Status}, AND(@cell <> "Cancelled", @cell <> "Hold", @cell <> "Meeting"),
Excluding the assignees looks like this...
=COUNTIFS({Status}, AND(@cell <> "Cancelled", @cell <> "Hold", @cell <> "Meeting"), {Assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"),
And finally... To be able to compare the dates in two separate columns, you will need to insert a helper column (checkbox for this example) and use a formula to check which rows meet that criteria.
=IF([End Date Estimated]@row < [Need By]@row, 1)
Then you would include this new range/criteria in your COUNTIFS on your metrics sheet.
=COUNTIFS({Status}, AND(@cell <> "Cancelled", @cell <> "Hold", @cell <> "Meeting"), {Assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"), {Helper Column}, @cell = 1)
-
Hi Paul,
I am still having trouble with both of the formulas.
=COUNTIFS({status}, <>"Complete", {status}, <>"Cancelled", {status}, <>"Hold", {assignee}, <>"Meeting", {assignee}, <>"Schedule Milestone", {Start Date Estimated}, ="")
Shows 35
=COUNTIFS({status}, AND(@cell <> "Cancelled", @cell <> "Hold", @cell <> "Meeting"), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"), {Start Date Estimated}, "")
Shows 43
=COUNTIFS({status}, AND(@cell <> "Cancelled", @cell <> "Hold", @cell <> "Meeting"), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"), {Start Date Estimated}, AND(@cell ""))
Shows Unparesable
The correct answer should be 58
=COUNTIFS({Start Date Actual}, ISBLANK(@cell), {status}, <>"Complete", {status}, <>"Cancelled", {status}, <>"Hold", {assignee}, <>"Meeting", {assignee}, <>"Schedule Milestone", {Start Date Estimated}, AND(@cell < TODAY(-5)))
This formula is also not pulling in blank- status's. I assume its around the same issue/format.
The second formula
=IF([End Date Estimated]@row < [Need By]@row, 1)
I added a column on the reference sheet and it shows Unparseable. Is that where I was supposed to add it?
Appreciate all your help!
-
Yes. It goes in a new column on the reference sheet. Make sure the column names are correct. I was just going by what you had in your last comment.
-
Hi Paul,
The column names do match.
Can you also advise what I am doing wrong with these formulas? I am trying to exclude status's Complete, Cancelled, and Hold and include all others (including blanks). Also to exclude meeting & schedule milestone from assignee. Looking to then count how many start date estimated are blank.
=COUNTIFS({status}, <>"Complete", {status}, <>"Cancelled", {status}, <>"Hold", {assignee}, <>"Meeting", {assignee}, <>"Schedule Milestone", {Start Date Estimated}, ="")
Shows 35
=COUNTIFS({status}, AND(@cell <> "Cancelled", @cell <> "Hold", @cell <> "Meeting"), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"), {Start Date Estimated}, "")
Shows 43
=COUNTIFS({status}, AND(@cell <> "Cancelled", @cell <> "Hold", @cell <> "Meeting"), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"), {Start Date Estimated}, AND(@cell ""))
Shows Unparesable
The correct answer should be 58
-
Sorry. I meant the one that is showing as unpareseable.
=IF([End Date Estimated]@row < [Need By]@row, 1)
-
Hi Paul,
Thanks for all your help and quick replies! I got that formula to populate in the reference sheet (you were right I forgot the word date in one of the fields names). However, now the below formula is advising "Invalid Ref"
=COUNTIFS({status}, AND(@cell <> "Cancelled", @cell <> "Hold", @cell <> "Complete"), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"), {Helper Column}, @cell = 1)
For the other formulas I realized one of the options was off for the top 2 now they both provide 35 (which does not include the blank status's). So I just need to figure out a way to add the blank status's into the formula.
-
To include the blank statuses, we just need to add that criteria into the status criteria set.
=COUNTIFS({status}, AND(@cell = "", @cell <> "Cancelled", @cell <> "Hold", @cell <> "Complete"), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"), {Helper Column}, @cell = 1)
As for the error... There are a number of things that could be causing this.
First check to see if any cell in any range contains that error.
If not, double check that each of the ranges are the same size and shape (only looking at a single column).
-
Hi Paul,
I tried the below 3 formulas and they are all returning 0, which is incorrect.
=COUNTIFS({Start Date Estimated}, ISBLANK(@cell), {status}, AND(@cell = "", @cell <> "Cancelled", @cell <> "Hold", @cell <> "Complete"), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"))
=COUNTIFS({status}, AND(@cell = "", @cell <> "Cancelled", @cell <> "Hold", @cell <> "Complete"), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"), {Start Date Estimated}, "")
=COUNTIFS({status}, AND(@cell = "", @cell <> "Cancelled", @cell <> "Hold", @cell <> "Complete"), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"), {Start Date Estimated}, AND(@cell "")
As for the Invalid Ref error for the below formula, I am not sure what you mean by if any cell in that range contains that error? I do see that the checkbox I added on the reference sheet did populate with some checks.
=COUNTIFS({status}, AND(@cell <> "Cancelled", @cell <> "Hold", @cell <> "Complete"), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"), {Helper Column}, @cell = 1)
-
Ok. Error first this time... Check every single cell that is being referenced throughout the formula. If that error is present in even one of them, it will pull through into your COUNTIFS.
It is hard without seeing the source data itself, but lets try this one real quick...
=COUNTIFS({Start Date Estimated}, ISBLANK(@cell), {status}, OR(@cell = "", AND(@cell <> "Cancelled", @cell <> "Hold", @cell <> "Complete")), {assignee}, AND(@cell <> "Meeting", @cell <> "Schedule Milestone"))
-
Got it all to work, thank you Paul!!!!
Sorry have another one for you! I got the below formula to work, however I would like to add if False or no match found then look at another reference.
=VLOOKUP(Building@row, {Client}, 2, false)
So something like =VLOOKUP(Building@row, {Client}, 2, false) = IFERROR(VLOOKUP(Building@row, {P Client}, 5, false))
-
Try this...
=IFERROR(VLOOKUP(Building@row, {Client}, 2, false), VLOOKUP(Building@row, {P Client}, 5, false))
Help Article Resources
Categories
Check out the Formula Handbook template!
