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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,586
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
58,586
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2011
Messages
6,518
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Insightful111

New Member
Joined
Apr 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
=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
Joined
Apr 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,133,625
Messages
5,659,941
Members
418,538
Latest member
alc51103

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
Top