Sum cells with multiple options from dropdown only

Colliers Marketing

Hello, I would like to sum up costs only for cells that have multiple homegrown fruits within a single month. In this example, January's sum would be $3,500. I've tried a few formulas and thought I got it with the below, but it doesn't look like it's accurate. Does anyone have any recommendations?

=SUMIFS({Inventory - Fruit}, AND(IF(COUNTM(@cell), >1)), {Inventory - Source}, "Home Grown", {Inventory - Date}, AND(IFERROR(MONTH(@cell ), 0) = 1, IFERROR(YEAR(@cell ), 0) = 2025))

Screenshot 2025-04-02 163502.jpg

Thanks in advance!

DKazatsky2

Hi @Colliers Marketing,

This should get you what you are after.

=SUMIFS({Inventory - Cost}, {Inventory - Fruit}, COUNTM(@cell ) > 1, {Inventory - Source}, HAS(@ cell, "Home Grown"), {Inventory - Date}, MONTH(@cell ) = 1, {Inventory - Date}, YEAR(@cell ) = 2025)

Note: The first argument in the SUMIFS is the column you want summed, I saw no mention of the "cost" column in the formula you provided.

Hope this helps,

Dave

Darren Mullen

@Colliers Marketing You can't use the AND function like that with cross sheet references within the SUMIFS formula.

Actually, you don't need the AND because SUMIFS is going to use AND logic for all the criterion ranges and criterion.

So you could do something like:

=SUMIFS({Inventory - Fruit}, {Inventory - Source}, has(@cell,"Home Grown"), {Inventory - Date}, AND(IFERROR(MONTH(@cell ), 0) = 1, IFERROR(YEAR(@cell ), 0) = 2025))

You could use the AND like that, although it's probably easier if you break it out like this because dates in cross sheet references are sometime finicky to work with:

=SUMIFS({Inventory - Fruit}, {Inventory - Source}, has(@cell,"Home Grown"), {Inventory - Date}, MONTH(@cell ) = 1, YEAR(@cell ) = 2025)

Colliers Marketing

Thanks @Darren Mullen ! I am looking to only add up the instances where there are multiple items in the cell (meaning the first and third rows of the above screenshot). Those formulas above add up all instances fruit inventory.

DKazatsky2

Hi @Colliers Marketing,

This should get you what you are after.

=SUMIFS({Inventory - Cost}, {Inventory - Fruit}, COUNTM(@cell ) > 1, {Inventory - Source}, HAS(@ cell, "Home Grown"), {Inventory - Date}, MONTH(@cell ) = 1, {Inventory - Date}, YEAR(@cell ) = 2025)

Note: The first argument in the SUMIFS is the column you want summed, I saw no mention of the "cost" column in the formula you provided.

Hope this helps,

Dave

Colliers Marketing

Thanks, Darren!