Count formula with cross sheet reference/criteria

ilene_healy68056

I’m sure this is easy and I am missing something fundamental.  I have searched other conversations but can’t debug my formula.

 

I have a detailed project sheet (that is at max capacity…therefore I have created a separate sheet to do some counts so I can create pie charts and dashboards.

I’d like to count the number of sites in a region and am running into the wrong value being returned or an error message.  I am referencing another sheet and am counting the number of sites in a region for the outline level 1….which was my way of counting unique values.  Can anyone advise me of my mistake?

 

This formula returns ‘0’ for NA, which is incorrect

COUNTIFS({MetricsRegion_Internal}, "NA", {MetricsOutlineLevelCount_Internal}, ="1")

 

This formula gives me an incorrect argument error

COUNTIFS({MetricsCountSite_Internal}, {MetricsRegion_Internal}, "NA", {MetricsOutlineLevelCount_Internal}, ="1")

 

Thanks!

Ilene

Andrée Starå

Hi Ilene,

Try this.

COUNTIFS({MetricsCountSite_Internal}, {MetricsRegion_Internal}, "NA", {MetricsOutlineLevelCount_Internal}, 1)

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

The first formula is giving you an incorrect count because you have quotes around the "1". Try removing those and see if that helps.

.

The second formula is returning an incorrect argument error because you are missing a criteria section.

 

You should have

=COUNTIFS(range 1, criteria 1, range 2, criteria 2, range 3, criteria 3)

You have

=COUNTIFS(range1, range 2, criteria 2, range 3, criteria 3)

 

Try correcting this and you should be squared away.

Andrée Starå

Oops! I missed the second formula! 

Paul Newcome

I almost missed it too. Initially I just wrote it off as a SUMIFS because of the syntax until I realized they were both COUNTIFS.

Andrée Starå

Thanks!

Now my level of shame is less! 

Paul Newcome

Hahaha,