Count formula with cross sheet reference/criteria

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

2019-06-25 10_42_45-SP Automation - Internal Sites - Smartsheet.com_.png

2019-06-25 10_43_26-Metrics Counts - Internal - Smartsheet.com_.png

Find more posts tagged with

Formulas

Comments

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,

Sign in to join the conversation!

Quick Links

Trending discussions

Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…
INDEX/COLLECT??
Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination. -Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times -Purpose of Device, there are…