What am I doing wrong with this Formula =COUNTIFS({Start Date Estimated}, "", {Start Date Estimate

dheerema@e15group.com

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?

Paul Newcome

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()))

dheerema@e15group.com

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()))

Paul Newcome

What are all of the statuses that you have?

Complete

Cancelled

On Hold

blank

what else?

dheerema@e15group.com

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}))

Paul Newcome

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)

dheerema@e15group.com

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!

Paul Newcome

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.

dheerema@e15group.com

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

Paul Newcome

Sorry. I meant the one that is showing as unpareseable.

=IF([End Date Estimated]@row < [Need By]@row, 1)

dheerema@e15group.com

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.

Paul Newcome

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).

dheerema@e15group.com

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)

Paul Newcome

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"))

dheerema@e15group.com

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))

Paul Newcome

Try this...

=IFERROR(VLOOKUP(Building@row, {Client}, 2, false), VLOOKUP(Building@row, {P Client}, 5, false))