Array in If statement

Insightful111

New Member
Joined
Apr 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
1619099171304.png


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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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)))
 
Upvote 0
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")))
 
Upvote 0
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)))
 
Upvote 0
Solution
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.
 
Upvote 0
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.
 
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,632
Members
452,661
Latest member
Nonhle

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?

Disable AdBlock

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
Back
Top