Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Lookup function in Countifs statement?
In my sheet I have a list of projects with various statuses and I need to do summary numbers by month and reduce the number of statuses to 2 (scheduled and unscheduled). I know how to do this if I use the Lookup function to populate a column with the converted statuses, then use Countifs to get the numbers for each month.
My question is, is there a way to do this in a single formula?
Here are samples of the formulas I'm using now:
Converting a row in the Proj State column (with lots of statuses) to one of 2 statuses using a Lookup (this formula is in the ConvertedStatus column on every project row):
=LOOKUP([Proj State]12, Primary$1:Result$5, 2, false)
Calculating a monthly number in a range of cells using the converted status and month # - the sample is for October:
=COUNTIFS($Month12:$Month62, 10, $ConvertedStatus12:$ConvertedStatus62, ="Unscheduled")
I'm trying to calculate the values in the yellow shaded columns in the screen shot.