Insightful111
New Member
 Joined
 Apr 22, 2021
 Messages
 4
 Office Version

 365
 Platform

 Windows
I have above sample data. In cells F9 and F10 I want to sum 'Sales' (C5:C10) for the respective months (Jan, Feb) based on the user input in cell F3.
With following formula I can get the sum of sales for an individual product per month: =SUMIFS(C5:C10,B5:B10,E9,A5:A10,F3).
To get the sum of sales for Fish&Hamster following array formula works: =SUM(SUMIFS(C5:C10,B5:B10,E10,A5:A10,{"Fish","Hamster"}))
However, how do I combine these in one formula to either see the sum of sales for an individual product or for Fish&Hamster combined depending on the user input in the list above?
I have tried following formula but it only takes into account the first product in curly brackets: =SUM(SUMIFS(C5:C10,B5:B10,E10,A5:A10,IF(F3=K6,{"Fish","Hamster"},F3)))
I assume it's something to do with if statements only considering one value.
Can you think of any other solutions to have one formula which sums sales per month based on user input for product (1 single product or combination of products)?
By the way this is just a sample data. I'm dealing with a large data set and multiple criteria based on different columns (only for 1 column I need to specify 2 criteria based on 1 column).
I hope this makes sense and thank you for your answers.