Can I use a manually populated list as a string array for sumifs criteria?

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Hello, thank you for taking to the time. I have searched, but the answers I found incorrectly assumes I will be making a selection from the list. I would like to avoid using VBA if possible because I want to be able to make duplicates of this spreadsheet, still have the formulas work, without worrying about whether the VBA and macros were disabled on another Office 13 installation.

The spreadsheet is as follows:
Column AV contains any numeric values of : $0 ~ $10,000.
Column A contains one category of : Fixed income or Variable income or Defrayment

This I know works and produces the result I desire...

=sum(sumifs(AV2:AV50, A2:A50, {"Fixed Income", "Variable Income", "Defrayment"} ) )

... but, rather than "statically" typing the string array, I want the array to be more dynamic by referencing a manually populated list and not requiring an item to be selected.

A1 would contain the manually populated list. To create this list, I used the Data Validation function in the Data menu and wrote in the source field : Fixed Income, Variable Income, Defrayment .

Something like this.

=sum(sumifs(AV2:AV50, A2:A50, {A1} ) )

I am guessing there is a function I do not understand yet to achieve my goal.

Can I use a manually populated list as a string array for sumifs criteria?

Or maybe I should avoid the manually populated list and simply enter a string array in A1? such as.. ={"Fixed Income", "Variable Income", "Defrayment"} .. but this does not work either.
 

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Jason, you beat me to it !! lol. I just now figured that out, but I don't understand why.

I suppose using the multiplier in your first solution is what forced the sumrange to be numeric only?
 

Some videos you may like

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,983
Office Version
  1. 365
Platform
  1. Windows
I suppose using the multiplier in your first solution is what forced the sumrange to be numeric only?
That is exactly the reason why, the multiplier is trying to perform a mathematical operation on the text which causes the error.
 

Maumbo

New Member
Joined
Jan 1, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Very cool man. Welp, thanks again! (y):cool:
Back to my spreadsheet crunching.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,080
Members
414,426
Latest member
fraru

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