Forum Discussion

Copper Contributor

Apr 05, 2023

Solved

using a column of geography data with formulas

Hi, I've created a map chart from a column of country names which has been converted to geography data. I would like to create a new column referencing the country name and looking up in an array in a separate sheet how many times the country name appears using a COUNTIF function. The formula returns 0 and appears to not be reading the country name. I have tested the formula by manually inserting a country name in a separate column and updating the formula to reference the new cell. Is there any way to reference the geography data country name to avoid this workaround? Thank you.

Charting

Formulas and Functions

SergeiBaklan
Apr 05, 2023
johkel

You may try SUMPRODUCT

4 Replies

SergeiBaklan

Diamond Contributor

Apr 05, 2023

johkel

=LET(
    names, UNIQUE(country[Country].Name),
    counts, MAP(
        names,
        LAMBDA(c,
            REDUCE(
                0,
                country[Country],
                LAMBDA(a,v, a + (v.Name = c))
            )
        )
    ),
    VSTACK({"Country","#"}, HSTACK(names, counts))
)

to generate

country count.xlsx36 KB

SergeiBaklan
Diamond Contributor
Apr 05, 2023
johkel

You may try SUMPRODUCT
- johkel
  Copper Contributor
  Apr 06, 2023
  SergeiBaklan thank you, I can confirm that .Name solves the reference issue. Much appreciated!
  - SergeiBaklan
    Diamond Contributor
    Apr 06, 2023
    johkel , you are welcome