Gathering data from 2 smartsheets into metrics sheet for dashboard

I am trying to take this formula and get the data 'CAD' from both smartsheets and place the amount of times in total that it appeared

What am i missing in my formula? =COUNTIFS({Behavior Analysis: 1S Range 1}, HAS(@cell,"CAD"), {Behavior Analysis: 1SShop Range 1}, HAS(@cell,"CAD")))

Anyone have any suggestions?

Find more posts tagged with

Formulas

Accepted answers

Mark.poole

@Cayla Davis

@DKazatsky2 had a slight syntax error. this should correct it

=COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada")) + COUNTIFS({Graphical Behavior Analysis: 1S Shop Fares Range 1}, HAS(@cell, "Canada"))

All comments

Mark.poole

@Cayla Davis Are you trying to get it to count for each instance it shows up in both of the reference ranges. or trying to get it to count ONLY if it shows in both.

DKazatsky2

Hi @Cayla Davis,

Try breaking it into 2 pieces.

=COUNTIFS({Behavior Analysis: 1S Range 1}, HAS(@cell,"CAD")) + =COUNTIFS({Behavior Analysis: 1SShop Range 1}, HAS(@cell,"CAD"))

Hope this helps,

Dave

Cayla Davis

@Mark.poole I am trying to get it to count each instance that it shows up for both references

Cayla Davis

@DKazatsky2 - Thank you, I did try that but i am getting INVALID OPERATION

=COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada")) + =COUNTIFS({Graphical Behavior Analysis: 1S Shop Fares Range 1}, HAS(@cell, "Canada"))

Mark.poole

@Cayla Davis

@DKazatsky2 Was close in his assessment. How ever there was a slight syntax error. Try

=COUNTIFS({Behavior Analysis: 1S Range 1}, HAS(@cell,"CAD")) + COUNTIFS({Behavior Analysis: 1SShop Range 1}, HAS(@cell,"CAD"))

Mark.poole

@Cayla Davis

@DKazatsky2 had a slight syntax error. this should correct it

=COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada")) + COUNTIFS({Graphical Behavior Analysis: 1S Shop Fares Range 1}, HAS(@cell, "Canada"))

DKazatsky2

Hi @Cayla Davis,

I had a typo in what I sent you, there was an extra "=". It should be this.

=COUNTIFS({Graphical Behavior Analysis: 1S Graphical Range 1}, HAS(@cell, "Canada")) + COUNTIFS({Graphical Behavior Analysis: 1S Shop Fares Range 1}, HAS(@cell, "Canada"))

Cayla Davis

Thank you so much @Mark.poole and @DKazatsky2 - appreciate both of you

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.