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

[Maumbo]

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.

 

[jasonb75]

You would need to enter your list into a range of cell (one item per cell) then set the formula as

=sum(sumifs(AV2:AV50, A2:A50,Z1:Z3))

where Z1:Z3 contains the list of items. Note that if you are working with any version of excel other than office 365 then this must be array confirmed with Ctrl Shift Enter otherwise a #VALUE! error will occur.

 

[Fluff]

Hi & welcome to MrExcel.
Can col A contain anything other than of those 3 values?

 

[Maumbo]

I am impressed with the speedy responses! Thanks again for your consideration.

You would need to enter your list into a range of cell (one item per cell) then set the formula as

=sum(sumifs(AV2:AV50, A2:A50,Z1:Z3))

where Z1:Z3 contains the list of items. Note that if you are working with any version of excel other than office 365 then this must be array confirmed with Ctrl Shift Enter otherwise a #VALUE! error will occur.

Thank you Jason! ...but... To use less workspace on the spreadsheet, I was hoping that your reference of "Z1:Z3" would fit into a single cell. For example, to stick with where i started, A1 data is a string converted to array to be used in the Sumifs.

I was surfing the internet, came across this. ... lets say this is contained in A1 cell, used ctrl+shift+enter ..
{=MID("Red", ROW(INDIRECT("1:" & LEN("Red"))),1}
this has an arrary output of {"R";"e";"d"}

For testing purposes, i temporarily changed the text value of 3 rows within A2:A50 to be "R", "e", "d". The corresponding row values are $2,500 , $46.60 , and $410. Total is $2,9560

Then using A1 array output in sumifs criteria.
=sum(sumifs(AV2:AV50, A2:A50, A1 ) )

This time I got a result, zero errors. Result was $2,500. So, I am "kinda" making progress. I tried ctrl+shift+enter, but with the same result. My intuition tells me I am inccorectly referencing A1 in the sumifs, maybe?

Maybe Jason has my only solution? Maybe my approach is wrong?

Hi & welcome to MrExcel.
Can col A contain anything other than of those 3 values?

Yes, in addition to "Fixed Income", "Variable Income", and "Defrayment", A2:A50 also contain string values of "Variable Expenses", "Variabled Budgeted Expenses", "Fixed Budgeted Expenses", "Fixed Expenses", "Fixed Discretionary Expenses", and "Deficit Management".

 

[jasonb75]

To use less workspace on the spreadsheet, I was hoping that your reference of "Z1:Z3" would fit into a single cell.

I would advise against it, that is number 2 on the list of bad ideas for a spreadsheet.
There are workarounds with variations of the formula that you came across but that in itself is another bad idea, the formula makes use of a volatile function which is also something that should ideally be avoided wherever possible.

If you insist on using a single cell for all of the criteria then I would suggest using sumproduct instead of sumif.

Your string in A1 should be something like Fixed Income,Variable Income,Defrayment Note that there are no spaces after the commas.

Then your formula would be

=SUMPRODUCT(ISNUMBER(SEARCH(","&A2:A50&",",","&A1&","))*AV2:AV50)

which would be less efficient than my previous suggestion but preferable to using volatile functions to split the string.

 

[Maumbo]

Wow, I am impressed, moreso by how this has been a great learning experience for me plus a great solution! Thank you Jason! I do appreciate the clarifying question too, Fluff. This is great!

I am just a simple small business farmer that momentarily use spreadsheets, rather than pencil and paper, to try to help reduce the time i spend in the office. I really appreciate y'all.

I would advise against it, that is number 2 on the list of bad ideas for a spreadsheet.
There are workarounds with variations of the formula that you came across but that in itself is another bad idea, the formula makes use of a volatile function which is also something that should ideally be avoided wherever possible.

"Volatile function". Greating learning experience right there. I did not understand why this was happening to me until your reference of "volatile function". I did some reading about how Indirect is a volatile function and now I understand why I had a bad experience some time back where an invoice got all screwed up ,which reflected bad on me with the customer. The invoice was using the Indirect function to grab values across workspaces and the function was not properly updating, although I did say to "auto update" wherever I could. I caught the mistake before the customer could complain, but this did not stop the customer from receiving a false invoice, that contained values lower than should have been if properly updated. This lead the customer thinking I was trying to swindle him, which he asked me about. From that moment on, I tried to keep my spreadsheets simple, but I found some complexity is still unavoidable.

If you insist on using a single cell for all of the criteria then I would suggest using sumproduct instead of sumif.

Your string in A1 should be something like Fixed Income,Variable Income,Defrayment Note that there are no spaces after the commas.

Then your formula would be

=SUMPRODUCT(ISNUMBER(SEARCH(","&A2:A50&",",","&A1&","))*AV2:AV50)

I do admit, that I fell in love with the sumifs function ever since I learned about it and I had tunnel vision. I totally forgot about the sumproduct function using arrays directly in its parameters. This makes perfect sense.

which would be less efficient than my previous suggestion

You are probably correct, but A1 will be a very small "hidden" row, that only me "as the author", know where it is located and why it is needed. I suppose I could do the same by following your initial suggestion with the extra reference "Z1:Z3" reference table and then hide it. I might just eventually do that and thank you for the solutions you provided.


Thanks again! I will for sure see what more I can learn here.

 

[Maumbo]

I forget to mention and have to say, I do appreciate this forum's software. In the past on other forums, I have had terrible experiences where doing multi-quotes would screw up my entire post and the quotes would become "broken". Hope i am not speaking too stoon, but I never experienced this once here, and this gave me confidence my message will be properly sent. Thank you again!

 

[jasonb75]

You are probably correct, but A1 will be a very small "hidden" row, that only me "as the author", know where it is located and why it is needed.

It could be A1:C1 if that is preferable, still only 1 row to hide. Although it is more common to have an extra sheet for such things then hide the whole sheet.
Another option would be to define the array as a named range that you could refer to (named ranges can refer to a formula or array as long as it is valid, not just a range).

Reference multi-quotes, they are most often broken when trying to edit the post with the quotes and making changes to the tags in error, it can still happen here but is less likely due to the tags being hidden by default.

 

[Maumbo]

It could be A1:C1 if that is preferable, still only 1 row to hide. Although it is more common to have an extra sheet for such things then hide the whole sheet.
Another option would be to define the array as a named range that you could refer to (named ranges can refer to a formula or array as long as it is valid, not just a range).

Reference multi-quotes, they are most often broken when trying to edit the post with the quotes and making changes to the tags in error, it can still happen here but is less likely due to the tags being hidden by default.

Excellent ideas!

Jason, just to be clear on my part, to ensure I did not misunderstand your efficiency statement... was this in reference to "ease of use" or that Excel takes longer to process the sumproduct solution?

I like this sumproduct function, but annoying on one particular limitation. The limitation that the "sum range" in the sumproduct function can not contain any text and so tosses out a #Value! error. I never encountered this problem with the Sumifs function. No worries, this is a simple data change on my part where I was using text, "TBD", in places where I knew a cost was going to happen once production reached a particular stage. The Sumifs function never did this to me.

Is there a workaround for this sumproduct function limitation? If not, no worries, i can make do without the "TBD".

 

[jasonb75]

With regard to efficiency, you're right that sumproduct would take longer to process than sumifs although with small ranges the difference would be negligible. Also in terms of efficiency, volatile functions will often be calculated / processed more frequently that should be necessary.

So that you can keep the TBD references where you need them, changing the sumproduct formula as below should work with text in the cells without causing errors.

=SUMPRODUCT(--ISNUMBER(SEARCH(","&A2:A50&",",","&A1&",")),AV2:AV50)