summing a number column if a certain selection is matched in a multiselect column

Ali Simpson

I have an Expense Intake sheet that has a multiselect column "Ships" where any number of ships can be selected; "Ship 1", "Ship 2", "Ship 3" etc. I also have a "Cost Per Ship" column on that sheet and a Month Column on that sheet

On my budget set up sheet I am trying to figure out how much was expensed to Ship 1 in January, but I feel the multiselect column on the Expense Intake Sheet is giving me troubles.

=SUMIFS({Month of Expense}, "Jan", {Expense Intake & List Range Ship}, Value@row, {Expense Intake & List Range Ship Cost})

But it is coming up as an Incorrect Argument Set, do I need to put in a CONTAINS?

Expense Intake sheet below

Budget Set up Sheet below

Paul Newcome

You will want a HAS function for the multi-select, but you also have the general syntax flipped around. In a SUMIFS (with the "S" on the end), the {Range To Sum} comes first. You have it last as if you are using a SUMIF (without the "S" on the end) instead.

Ali Simpson

Thanks Paul for the heads up, I am just needing a little more guidance on where the HAS should be entered

I adjusted the formula to below but it is still coming out as Incorrect Argument Set. I did realize I needed a little more criteria through the formula as I only wanted the expenses from 2025 in this, so I added that but I'm not sure if that threw me off more.

=SUMIFS({Expense Intake & List Range Ship Cost}, {Month of Expense}, "JAN", {Expense Intake & List Range 3}, >=DATE(2024, 12, 31), {Expense Intake & List Range 3}, <=DATE(2026, 1, 1), HAS({Expense Intake & List Range Ship}, Value@row))

Paul Newcome

Try this syntax:

=SUMIFS({Range To Sum}, {Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERRORYEAR(@cell), 0) = 2025), {Multi-Select Range}, HAS(@cell, Value@row))

Ali Simpson

Thank you for this Paul, can I ask what the @cell after the HAS means in the syntax?

Paul Newcome

It is basically the same as the @cell in the MONTH and YEAR functions. Take the previously established range and evaluate it on a cell by cell basis.