Array in If statement

[Insightful111]

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.

 

[mrshl9898]

=IF(F3=K6,SUM(SUMIFS(C5:C10,B5:B10,E10,A5:A10,{"Fish","Hamster"})),SUM(SUMIFS(C5:C10,B5:B10,E10,A5:A10,F3)))

 

[Fluff]

Your formula works for me

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3						Fish&hamster
4
5	Fish	Jan	10
6	Hamster	Feb	20								fish&hamster
7	Dog	Jan	13
8	Fish	Feb	40
9	Hamster	Jan	15		Jan	25
10	Dog	Feb	30		Feb	60
11
Result

Cell Formulas
Range	Formula
F9:F10	F9	=SUM(SUMIFS($C$5:$C$10,$B$5:$B$10,E9,$A$5:$A$10,IF($F$3=$K$6,{"Fish","Hamster"},$F$3)))

 

[Fluff]

Another option might be in col G

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2
3						Fish&hamster
4
5	Fish	Jan	10
6	Hamster	Feb	20
7	Dog	Jan	13
8	Fish	Feb	40
9	Hamster	Jan	15		Jan	25	25
10	Dog	Feb	30		Feb	60	60
Result

Cell Formulas
Range	Formula
F9:F10	F9	=SUM(SUMIFS($C$5:$C$10,$B$5:$B$10,E9,$A$5:$A$10,IF($F$3=$K$6,{"Fish","Hamster"},$F$3)))
G9:G10	G9	=SUM(SUMIFS($C$5:$C$10,$B$5:$B$10,E9,$A$5:$A$10,FILTERXML("<k><m>"&SUBSTITUTE($F$3,"&","</m><m>")&"</m></k>","//m")))

 

[FormR]

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)))

Hi, your version of Excel probably requires Control+Shift+Enter for that formula - a workaround would be to swap sum() for sumproduct()

=SUMPRODUCT(SUMIFS(C5:C10,B5:B10,E10,A5:A10,IF(F3=K6,{"Fish","Hamster"},F3)))

 

[Insightful111]

=IF(F3=K6,SUM(SUMIFS(C5:C10,B5:B10,E10,A5:A10,{"Fish","Hamster"})),SUM(SUMIFS(C5:C10,B5:B10,E10,A5:A10,F3)))

Thank you. It's a long formula as I have multiple criteria but it worked for me so thank you very much.

 

[Insightful111]

Hi, your version of Excel probably requires Control+Shift+Enter for that formula - a workaround would be to swap sum() for sumproduct()

=SUMPRODUCT(SUMIFS(C5:C10,B5:B10,E10,A5:A10,IF(F3=K6,{"Fish","Hamster"},F3)))

Yes, you are right. My formula worked when I entered Control+Shift+Enter but I like your formula using sumproduct even better. Thank you for help.

 

[Insightful111]

Your formula works for me

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3						Fish&hamster
4
5	Fish	Jan	10
6	Hamster	Feb	20								fish&hamster
7	Dog	Jan	13
8	Fish	Feb	40
9	Hamster	Jan	15		Jan	25
10	Dog	Feb	30		Feb	60
11
Result

Cell Formulas
Range	Formula
F9:F10	F9	=SUM(SUMIFS($C$5:$C$10,$B$5:$B$10,E9,$A$5:$A$10,IF($F$3=$K$6,{"Fish","Hamster"},$F$3)))

Yes, as other poster advised me, it works when I enter control+shift+enter. Thank you for your response.

 

[Aladin Akyurek]

View attachment 37317

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.

Control+shift+enter, not just enter (if needed)

=SUM(IF(ISNUMBER(SEARCH($A$5:$A$10,$F$3)),IF($B$5:$B$10=E9,$C$5:$C$10)))