COUNTIF with NETWORKDAYS & TODAY - Counting Aging Orders in Sheet Summary Field

JJ Coates

Hi Folks,

I'm trying to set up sheet summary fields to count the number of orders of varying ages in a pending orders smartsheet. As far as I've been able to tell, there's no way to calculate it by hours, so I've settled for "order submitted today", 1 day old, 2 days old, 3+ days old.

The problem is that I would like this in business days so that our stats aren't thrown off by the weekend. As far as I can tell the best way to do this is with NETWORKDAYS, but I'm struggling with the syntax in the COUNTIF statement in the sheet summary field to calculate the number of business days.

I've tried a number of different variations, all of which - when they parse - will only return 0 and I can't figure out what I'm doing wrong.

This in-cell calculation is working correctly in a test cell to calculate network days:

=NETWORKDAYS([Order Submitted]@row, TODAY())

but when I try and use it as a criteria in the COUNTIF function, it always returns 0 (unless it's #UNPARSABLE). This is the current iteration that is returning 0:

=COUNTIF([Order Submitted]:[Order Submitted], "NETWORKDAYS([Order Submitted]@row, TODAY()) < 1")

I've clearly misunderstood how criteria work in a COUNTIF function, but I can't figure out how to rectify it and all my frantic googling is turning up nothing.

Thanks for your help!

Cheers,

David Joyeuse

Hi @JJ Coates

Remove the quotes around your criteria in your COUNTIF formula. Quotes here means smartsheet is looking for this specific string of characters. Which he'll never find, hence the answer 0 :)

=COUNTIF([Order Submitted]:[Order Submitted], NETWORKDAYS([Order Submitted]@row, TODAY()) < 1)

That should be better :)

Hope it helped!

JJ Coates

Hi David!

Thanks so much for your assistance - unfortunately it's coming back as #UNPARSEABLE - which has me suspecting that maybe the COUNTIF statement won't accept a calculating function within it?

If all else fails, I can make a helper column that calculates the NETWORKDAYS of each order and then run my sheet summary fields with that column as the reference, but if it can all be contained in one formula that would be my preference.

Thanks again for your help

earl_bennett

Did anyone find the answer to this?

This formula always returns zero

=COUNTIF([Date1]:[Date1], NETDAYS([Date1]@row, [Date2]@row) > 1)

but the NETDAYS part of this formula returns 11

NETDAYS([Date1]@row, [Date2]@row)

Thanks,