Count occurrence of a value in another sheet

Jacob A

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?

NickStaffordPM

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