Formula help

Cathy Fraser

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

Paul Newcome

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

Cathy Fraser

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

 

Paul Newcome

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