Welcome!

Help with Formula for Data from 2 Columns

Screen Shot 2020-03-30 at 12.19.20.png

Help! Trying to display on dashboard specific date from two columns.

The Internal / External Column has two drop down options namely Internal and External

The Tier Column has six drop down options namely Office of CEO, HRC, Tier 1, Tier 2, Tier 3 and Tier 4

I can't get the formula to work that will show for example how many Internal Tier 2 there are or how many External HRC. Thanks

Chris

Find more posts tagged with

Comments

Paul Newcome

You are going to want your formulas to look something like this...

=COUNTIFS([Internal / External]:[Internal / External], "Internal", [Tier Column]:[Tier Column], "Tier 2")

Chris Moore

Thanks Paul

Struggling when I am adding this to a separate sheet and therefore needing to reference the two columns in the formula.

Thanks

Paul Newcome

Start typing

=COUNTIFS(

In the helper box that pops up below the cell you are working in, click on the link that says "Reference Another Sheet".

Select the sheet you want to reference, then click on the column header for the [Internal / External] column.

When you click on "Insert Reference" in the bottom right corner, it will take you back to the sheet you are working in, and you should now see something along the lines of...

=COUNTIFS({Sheet Name Range 1}

Throw in your comma, the criteria, then another comma:

=COUNTIFS({Sheet Name Range 1}, "Internal",

Then use the above steps to select the other column.

=COUNTIFS({Sheet Name Range 1}, "Internal", {Sheet Name Range 2}

THen you can just finish out the formula, and you should be good to go.

=COUNTIFS({Sheet Name Range 1}, "Internal", {Sheet Name Range 2}, "Tier 2")

Chris Moore

Massive thanks

Final question - Do you know of a mapping solution for a dashboard that can map data from Smartsheet. Referring to country location data that I would like to map.

Thanks

Paul Newcome

Hmm... That's a good question. I do know that you can enter Street Address, City, State, and Zip into a Smartsheet and then create a dynamic (automatically updating) link to an Excel spreadsheet.

From there you can load the Excel spreadsheet as one of "Your Maps" into Google Maps. I am not sure if/how other mapping programs accept Excel sheets or how to automate the upload of the Excel sheet into the mapping program other than manually.

I am also not sure what mapping programs can be displayed on a Smartsheet Dashboard using a Web Content Widget, but you can grab the URL from your mapping program and use it in a Shortcut Widget.

Sign in to join the conversation!

Quick Links

Trending discussions

Adding sequetial number string
Hello I could use some help with this one. I got the formula to work in the "number column"…. but I cant currently get it to work as a column formula. Basically what I am trying to achieve is have it add a number in order if the "footer comp" column is unchecked. and miss is unchecked Right now it is counting but in the…
Average with exceptions
I'm need to apply a function to average a column (answer is correct (8)) if the other column (The nurse is caring for a a patient who dove into a) is NOT blank. I copied this over from an excel spreadsheet. I would like to keep the average in the top row. Right now it's saying "#unparseable" as the formula is not the same…
Need help with INDEX COLLECT Formula
This discussion was created from comments split from: How do I combine multiple INDEX Collect formulas in one Formular?.