Count occurrence of a value in another sheet

Options

Hi,

I am trying to count the occurrence of a value in one sheet and report in another sheet.

For example, sheet A, with over 500 records, contains columns Unit Type and Country while Sheet B contains columns Country, Unit Type - Family and Unit Type - Single.

The Unit Type column has either Family or Single. Country column has 5 different values; France, Spain, Italy, England, Monaco.

On sheet B, I want to Unit Type - Family to count number of Family appears for each Country. And Unit Type - Single to count number of Single appears for each Country.

I tried using cell reference on Sheet B to Sheet A to do the count but I keep getting 0 for all the countries. =COUNTIFS({Sheet A Country}, [Country]@row, {Sheet A Unit Type}, "Family") and =COUNTIFS({Sheet A Country}, [Country]@row, {Sheet A Unit Type}, "Single")

Any suggestions please?

Answers

  • NickStaffordPM
    NickStaffordPM ✭✭✭✭✭
    Options

    So a few things, first, try and put all counting criteria in one formula like the following:

    =COUNTIFS({Sheet A Country}, [Country]@row, {Sheet A Unit Type}, "Family",{Sheet A Unit Type}, "Single")

    That way you have three distinct criteria/criteria range combos that can be looked at sequentially by Smartsheet

    {Sheet A Country}, [Country]@row

    {Sheet A Unit Type}, "Family"

    {Sheet A Unit Type}, "Single"

    Additionally, the [County]@row will likely be an issue because it does not select one criteria to look for, you will have to either specify which country you want, "Canada" for example, or create a sheet summary metric and reference that instead

    Using Canada as an example, the formula would look like the following:

    =COUNTIFS({Sheet A Country}, "Canada", {Sheet A Unit Type}, "Family",{Sheet A Unit Type}, "Single")

    Let me know if this does not help!

    Thanks

    Nick

    Thanks!

    Nick Stafford

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!