SUM(COLLECT/SUMIFS( based on multi-select

TheNickOdeh

Hi everyone,

Does anyone have a formula already handy that does this? Looking to SUMIFS values where the variable is one of the options in the dropdown value?

This is a cross sheet formula pulling from a table where there is only one country per cell, but looking to aggregate (show me everything in this region).

Paul Newcome

Switch the pieces inside of the HAS function around like I have in my example.

Generally speaking, the has function is more designed to work with finding a single text string in a range of multi-select cells. In this case you are working backwards. You are wanting to find the options within a single multi-select cell in a series of text string cells. Flipping the search value and range around backwards should give you backwards functionality.

Paul Newcome

You will need to incorporate a HAS function.

HAS(Country$1, @cell)

TheNickOdeh

@Paul Newcome yeah, I went with:

=SUM(COLLECT({Cost}, {Effective Date}, ISDATE(@cell), {Effective Date}, YEAR(@cell) = Year@row, {Effective Date}, MONTH(@cell) = Month@row, {Country}, HAS(@cell, Country$1))) that turns the values to 0 once I add a 2nd country.

Paul Newcome

Switch the pieces inside of the HAS function around like I have in my example.

Generally speaking, the has function is more designed to work with finding a single text string in a range of multi-select cells. In this case you are working backwards. You are wanting to find the options within a single multi-select cell in a series of text string cells. Flipping the search value and range around backwards should give you backwards functionality.

TheNickOdeh

My man! I appreciate you.

Paul Newcome

Happy to help. 👍️