Help with Formula for Data from 2 Columns

Chris Moore

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

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.