# Array in If statement

#### Insightful111

##### New Member

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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### mrshl9898

##### Well-known Member
=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

##### MrExcel MVP, Moderator
Your formula works for me
+Fluff 1.xlsm
ABCDEFGHIJK
1
2
3Fish&hamster
4
5FishJan10
6HamsterFeb20fish&hamster
7DogJan13
8FishFeb40
9HamsterJan15Jan25
10DogFeb30Feb60
11
Result
Cell Formulas
RangeFormula
F9:F10F9=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

##### MrExcel MVP, Moderator
Another option might be in col G
+Fluff 1.xlsm
ABCDEFG
1
2
3Fish&hamster
4
5FishJan10
6HamsterFeb20
7DogJan13
8FishFeb40
9HamsterJan15Jan2525
10DogFeb30Feb6060
Result
Cell Formulas
RangeFormula
F9:F10F9=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:G10G9=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

##### MrExcel MVP

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

##### New Member
=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

##### New Member

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

##### New Member
Your formula works for me
+Fluff 1.xlsm
ABCDEFGHIJK
1
2
3Fish&hamster
4
5FishJan10
6HamsterFeb20fish&hamster
7DogJan13
8FishFeb40
9HamsterJan15Jan25
10DogFeb30Feb60
11
Result
Cell Formulas
RangeFormula
F9:F10F9=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.

##### MrExcel MVP
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)))

Replies
13
Views
253
Replies
6
Views
310
Replies
3
Views
595
Replies
3
Views
101
Replies
1
Views
490

1,136,909
Messages
5,678,514
Members
419,768
Latest member
eguechi09x

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back