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.
 
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?
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
Very cool man. Welp, thanks again! (y):cool:
Back to my spreadsheet crunching.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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