Formula help

Options
✭✭✭✭✭
edited 12/09/19

Hi, I am trying to extend a formula to look at a second Criterion in a spreadsheet. the current formula is

="NSW (" + COUNTIF(CHILDREN(State6:State61), "NSW") + "), QLD (" + COUNTIF(CHILDREN(State6:State61), "QLD") + "), NT (" + COUNTIF(CHILDREN(State6:State61), "NT") + "), WA (" + COUNTIF(CHILDREN(State6:State61), "WA") + "), SA (" + COUNTIF(CHILDREN(State6:State61), "SA") + "), VIC (" + COUNTIF(CHILDREN(State6:State61), "VIC") + "), TAS (" + COUNTIF(CHILDREN(State7:State61), "TAS") + "), NSO (" + COUNTIF(CHILDREN(State6:State61), "NSO") + ")"

Now I know I will need to go COUNTIFS to pick up the second criterion, but it does not seem to work

I have put a few screen shots in. the first one shows the results of all projects that =NSW, but I want it to only show the results if it = NSW and the Project stage is Project WIP as shown in the second snap shot

Hope someone can help me.

Thanks

Tags:

• ✭✭✭✭✭✭
Options

When using the COUNTIFS for this case, you will want to use an @cell reference. See below:

COUNTIF(CHILDREN(State6:State61), "NSW", CHILDREN([Project Stage]6:[Project Stage]61), @cell = "Project WIP")

• ✭✭✭✭✭
Options

Thanks Paul, with a little bit of work the above worked out.

• ✭✭✭✭✭✭
Options

Happy to help. I just realized I forgot to make it COUNTIFS instead of COUNTIF. Glad you figured that out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!