• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMIFS with choices of bracketed text

Apaka

New Member
Hi. I'm at wits end trying to figure out what I'm doing wrong (I have to believe this is possible). I have multiple scenarios I need to pick to sum various elements of a table. There are 14 scenarios to sum, so a nested IF statement would be extremely painful. Attached is a sample of the file, where I may want to sum "Base" only, or "Base" and "EDI", all three, or other combinations (of the sample; the real version is much more expansive).

I would like to set up a drop-down of options, which would then sum Column D. The solution will also be applied to summing a few other columns as well.

I've tried referencing the cell with bracketed text, SHIFT+CTRL+ENTER, and other things.

Any help is appreciated. I've tried to do this before with something but couldn't figure it out so nested an IF statement. Now I need this with more options so nesting is something I am hoping to avoid.

I cannot use VBA to solve (I need the file to stay an XLSX).

Thank you in advance.
 

Attachments

  • Bracket text.xlsx
    27 KB · Views: 11
You could create a Pivot Table and use slicers ( depending on your version of XL there are possible options which I don't have)
 
Thank you for the suggestion. I initially considered a pivot table but need something a little cleaner and more summarized than a pivot table will give me.

I will end up with a summary table on one chart and a pivot table won’t let me do that. No need for slicers either.
 
Thank you for the suggestion. I initially considered a pivot table but need something a little cleaner and more summarized than a pivot table will give me.

I will end up with a summary table on one chart and a pivot table won’t let me do that. No need for slicers either.

Also, there are other data elements from a different table that are going to be incorporated and will require simple addition of the results of each table. Unfortunately a power pivot would be very unwieldy and busy.
 
Apaka
... to sum that column with many variations
could You use =AGGREGATE(9,7,OM[AF2.4 ($K)]) to get Your needed sum?
Those variations You could get with Filters or Slicers Your table.
 
Apaka
... to sum that column with many variations
could You use =AGGREGATE(9,7,OM[AF2.4 ($K)]) to get Your needed sum?
Those variations You could get with Filters or Slicers Your table.

I may not be understanding your recommendation. While AGGREGATE gets me information, I don't see how it gives me options to choose among a myriad of summarized information. I would be looking to sum Col D, filtering on Col B, K, and A. For Col A, I need to be able to select one of the following choices:


Base
Base, BTO
Base, EDI
Base, OCO
Base, BTO, EDI
Base, BTO, OCO
Base, EDI, OCO
Base, BTO, EDI, OCO
BTO
BTO, EDI
BTO, OCO
BTO, EDI, OCO
EDI
EDI, OCO

Pivot tables aren't an option for me; I'm not sure where the slicers would come in to play here.

Is there not a solution that allows the choices above to replace the bracketed text in the following formula:
=SUM(SUMIFS(OM[AF2.4 ($K)],OM[FUND],{"Base","OCO"},OM[CMD],"AFC"))

I appreciate any other thoughts or potential solutions on this.
 
Apaka
as above ...
> You can do Your FUND-combinations as You want
> You can do Your CMD-combinations as You want
> You can do Your IT MDEP-combinations as You want ( I cannot - You know )
What is missing? ... the result is in cell O5.
Screenshot 2021-03-22 at 17.48.30.png
... same can do with Filter.
 

Attachments

  • Bracket text.xlsx
    30.5 KB · Views: 5
Apaka
as above ...
> You can do Your FUND-combinations as You want
> You can do Your CMD-combinations as You want
> You can do Your IT MDEP-combinations as You want ( I cannot - You know )
What is missing? ... the result is in cell O5.
View attachment 73845
vletm,

I can certainly see through your eyes why the AGGREGATE function and slicers is a great idea. I will fully admit I may not be connecting all the dots so I’m hoping this helps a little more (and have attached a more complete file).

To provide some context, I would like select 1 of 14 “Funds” options. Based on the “Funds” option, I am hoping to see the different results. In theory, I should be able to use a drop-down (cell B2) or slicer (based on your recommendation) to feed/filter different tabs.

I want to select 1 of the 14 options and determine the data reported back. The selection would sum an office’s funds (OM[AF2.4 ($K)]), based on the office name ([@Office]), and may or may not include “Base”, “BTO”, “EDI’, “OCO”, or some combination thereof. From what I have seen however, slicers don’t get me the unique individual results. Again, I may very well have not connected the dots or not connected them the right way.

I have tried to slim down a copy of the file with a more complete picture but can't seem to get it to a smaller file size (it is staying at 1.8MB for some reason even though I have removed tables and worksheets, converted other tables with formulas to values, etc).
Again, while the slicers show totals, they aren't giving me the results I need to see.

Thank you for trying to help.
 
Apaka
You're writing I ( Apaka ) am hoping to see the different results...
and I ( vletm ) am hoping to see correct results ... numbers don't lie ... ?
So far, You could do with my sample any combinations.
What are You expected different results?
I tested to get now, those four from Your given sample file:
O) if all FUNDs has selected
P) if ONLY BASE has selected
Q) if ONLY EDI has selected
R) if ONLY OCD has selected
Screenshot 2021-03-23 at 09.02.50.png
If Your 'different' results are ... different ... then let me know which rows should sum?

(( I cannot use IT MOEP-column for those values - because all of those are #REF ))

based on the office name ([@Office])
... interesting, I cannot find above term at all.
I won't comment this part more.

What do You write about 1.8MB-file?
If You cannot get correct results with Your given sample file, then how would those or any results get from other file?
 
Apaka
You're writing I ( Apaka ) am hoping to see the different results...
and I ( vletm ) am hoping to see correct results ... numbers don't lie ... ?
So far, You could do with my sample any combinations.
What are You expected different results?
I tested to get now, those four from Your given sample file:
O) if all FUNDs has selected
P) if ONLY BASE has selected
Q) if ONLY EDI has selected
R) if ONLY OCD has selected
View attachment 73849
If Your 'different' results are ... different ... then let me know which rows should sum?

(( I cannot use IT MOEP-column for those values - because all of those are #REF ))

based on the office name ([@Office])
... interesting, I cannot find above term at all.
I won't comment this part more.

What do You write about 1.8MB-file?
If You cannot get correct results with Your given sample file, then how would those or any results get from other file?

Without a doubt this is challenging trying to sort this out via a forum, so thank you for being patient with me. Yes, I created confusion as I tried to explain based on my current model. I have re-created a very generic model without any formatting to get the size down.

To provide some context, I would like to use the “Variables” tab to select 1 of 14 “Funds” options (for this, shown on the “Variables” tab). Based on the “Funds” option selected (“Variables” tab, cell B2), I would want the selection to then expand or contract (limit) funds from “OM” column A. If you look at tabs “2”, “3”, or “4”, columns B and C show one of the criteria as “Base”. Based on changing variables (“Base”, “EDI”, combinations, etc), that will affect what is reported and summarized on “Summary”, “2”, “3”, and “4” (FYI, there are more scenarios (than 2-4).

From the “Variables” tab, I should be able to use the drop-down (cell B2) or slicer (based on your recommendation) to feed/filter tabs “Summary”, but I don’t know that the slicers are returning anything other than the funds, but not at the office level.

Attached is a very streamlined file that hopefully makes this more understandable. Again, I know it’s hard doing this via text only.

Thank you in advance.
 

Attachments

  • Pro-Rata Example File 3.xlsx
    185.6 KB · Views: 3
Apaka
Okay ...
You could not answer to my question about four values ... or should it be okay?
... and suddenly You change the whole skenario ... to different or who knows?
I should wish You good luck.
 
Apaka
Okay ...
You could not answer to my question about four values ... or should it be okay?
... and suddenly You change the whole skenario ... to different or who knows?
I should wish You good luck.

I appreciate you trying. Of note, the sample file does show what I was looking for in results. I'm not summing by MDEP, or just Base, or just EDI, etc. If you look at the SUMIFS in tabs 2, 3, or 4, you'll see they are all using the criteria of just Base. The question was, and has been, if there are combinations.

In closing , do you know that is possible.

All the best.
 
I appreciate you trying. Of note, the sample file does show what I was looking for in results. I'm not summing by MDEP, or just Base, or just EDI, etc. If you look at the SUMIFS in tabs 2, 3, or 4, you'll see they are all using the criteria of just Base. The question was, and has been, if there are combinations.
In closing , do you know that is possible.
All the best.

This formula option based on your #1 post file and #6 post choice.

In Q2, enter formula :

=SUMPRODUCT(SUMIFS(D:D,A:A,FILTERXML("<a><b>"&SUBSTITUTE(R2,", ","</b><b>")&"</b></a>","//b"),B:B,"AFC"))

73866
 

Attachments

  • Bracket text (by).xlsx
    27.2 KB · Views: 4
Last edited:
This formula option base on your #1 post file and #6 post choice.

In Q2, enter formula :

=SUMPRODUCT(SUMIFS(D:D,A:A,FILTERXML("<a><b>"&SUBSTITUTE(R2,", ","</b><b>")&"</b></a>","//b"),B:B,"AFC"))

View attachment 73866

Thank you so much. While I need to finish working this into my model, it looks like that's exactly what I was looking for. Thank you again. Sincerely.
 
Last edited by a moderator:
Back
Top