Formula counting items in red and yellow

Sonia.DomGon

Hi,

I need a formula to count how many items are overdue in each department?

=COUNTIFS({Test & Tag Register Range 1}, "paint", {Test & Tag Register Range 2}, "red")

Range 1 is the column referring to departments and Range 2 is the due date.

I believe is does not recognise "red". Could you assist with this issue?

Thank you

Kelly Moore

Hey @Sonia.DomGon

You're right, your formula is not picking up the "red". If you click the dropdown in your RAG ball column, you will see how the colors are spelled. Text strings are case sensitive when enclosed in quotes as you have above.

=COUNTIFS({Test & Tag Register Range 1}, "paint", {Test & Tag Register Range 2}, "Red")

You may want to double check the spelling/case of "paint"

Does this work for you?

Kelly

Sonia.DomGon

Hi Kelly,

Thanks for your reply- but unfortunately, it does not work. Noted that the colour red is part of a conditional formatting rule.

Any alternative or solution? Thanks again :)

Kelly Moore

Hey Sonia

Sorry, I misunderstood. Currently, smartsheet doesn't have the functionality to use conditional format settings in its formulas. What criteria is being used to generate the conditional formatting? You should be able to use that in your formula. You will need to add helper columns (checkbox columns will do) to indicate your colors if you cannot filter the data otherwise.

If you can share your conditional formatting and a screenshot (remove sensitive info), I can help you with your specific formula

Kelly

Sonia.DomGon

Oh, okay-what a shame :( and it will be really simple. (see attached the screenshot of my reference sheet.

Red means overdue

Yellow means its due in 2 weeks time.

I am trying to get the count of how many items per department are overdue and how many items per department are going to be due.

Thank you Kelly.

Sonia.DomGon

In other words:

I want something like Assembly has 9 items that are overdue and Machine Shop has 5, etc...

Kelly Moore

A "Red" Report and a "Yellow" Report would be the simplest way to summarize if you want each Location called out. The list of Locations would be dynamic - if you added additional locations they would be added to your report. Reports can be added to Dashboards using the Report widget. If I was doing this, this is how I would do it.

That being said, if you only have a handful of Locations you could build the individual COUNTIFS for each Location - one Red, one Yellow.

Here's the formulas

Paints (Red)

=COUNTIFS({Test & Tag Register Location column}, "Paints", {Test & Tag Register Next Inspection Due Date}, ISDATE(@cell), {Test & Tag Register Next Inspection Due Date}, @cell<TODAY())

Paints (Yellow)

=COUNTIFS({Test & Tag Register Location column}, "Paints", {Test & Tag Register Next Inspection Due Date}, ISDATE(@cell), {Test & Tag Register Next Inspection Due Date}, @cell=TODAY(14))

When you say that a yellow is due in two weeks, does that mean it's due in exactly 14 days, or it's due from now up to 14 days (for example it might be due in 5 days)? I have the formula written for exactly 14d but it is easily changed.

To build the Report, select the desired sheet and bring in the columns you want to see.

Yellow report (Note I have the report filtering exactly 14 days)

The Red Report changes the Filter to [Next Inspection Due Date] is In The Past. The grouping and summary stay the same.

Does this work for you?

Kelly

Sonia.DomGon

Hi Kelly,

This explanation has been really helpful.

The formula for red is working properly. The yellow one is giving me the wrong data. The conditioning is been set for 14 days but the formula above is not giving me the right data. What do you think it might be the issue?

Thanks again

Kelly Moore

Hey

This is what I was wondering - did you mean the yellow was when a date was exactly 2wks out, or was it within the next two weeks. Your yellow condition is everything within the next two weeks. We need to remove the report filter (the second one in the filter list) that says it is not within the next 13 d. Then your report filter and your conditional format filter will be equivalent

Kelly

Sonia.DomGon

Hi Kelly,

Thanks for your reply-I am doing the formulas for now:

=COUNTIFS({Test & Tag Register Location column}, "Paints", {Test & Tag Register Next Inspection Due Date}, ISDATE(@cell), {Test & Tag Register Next Inspection Due Date}, @cell=TODAY(14))

I believe the equal is giving me the wrong data. Any clue? Thanks again :)

Kelly Moore

Hey Sonia

Try this

=COUNTIFS({Test & Tag Register Location column}, "Paints", {Test & Tag Register Next Inspection Due Date}, ISDATE(@cell), {Test & Tag Register Next Inspection Due Date}, AND(ISDATE(@cell), @cell<=TODAY(14),@cell, @cell>TODAY()))

I added the ISDATE(@cell) to insure that the cells you are counting have valid dates within them.

You don't have anything in this formula to turn 'off' the counting - this will be more important on your Red counts as eventually, as time marches forward, all dates will be in the past. Do you have an indicator to mark a row as 'complete'. You need to exclude rows that are completed.

Does this work for you?

Kelly

Sonia.DomGon

Hi Kelly,

This works :) can't thank you enough!

Kelly Moore

Hey

Glad to help. Were you able to add criteria to ignore the rows that were completed, using whatever indicates 'completed' in your process?

Sonia.DomGon

Hi Kelly,

It works properly :)

Now, I got another challenge question that you might be able to resolve it? I am trying to calculate the total number of business days (therefore, excluding Sat & Sun) from the above dates. I did my research and NETWORKDAYS is the formula I should be using?

Thanks for your help!

Kelly Moore

Hey

Yes, NETWORKDAYS is the formula to use.

As you saw in your research, the difference between NETWORKDAYS and NETWORKDAY is one day. NETWORKDAY adds another day to the total. I don't think I've ever used NETWORKDAY (singular), I default to NETWORKDAYS.

cheers

Kelly

Sonia.DomGon

Hey,

Thanks for your reply. The formula is working but is there any way that it gets pulled to a calendar view? If I change from a grid view to a calendar view- how could I get rid of Sat & Sun?