Nesting IF functions with SUMPRODUCT to create a TOTAL option in a custom list

[Rewcifer]

First off, this is my first time posting, so sorry if I'm missing any important guidelines or formatting! I've been working on a small tool at work that I'm trying to make as turnkey as possible. I'm attempting to use the SUMPRODUCT function to parse my data by two separate custom lists I've created. As with custom lists, the "total" option has to be written manually into the formula, which I was able to achieve with just one custom list. Adding the second one is a little more difficult, as the formula keeps returning 0. The logic I want this formula to follow is to just return the total for the data being pulled if both lists = "total." I'm also trying to make this formula dynamic in the sense that if one of the drop downs = "total," one can still parse out the data further with the other drop down. Any and all help is appreciated on where I am going wrong! My hunch is that I may need one more IF statement, but I'm unsure. Thank you all in advance!
The formula that works is as follows:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=IF($B$1="Total",SUMPRODUCT(--('Gap Analysis data
Pull'!$J$2:$J$362=Matrix!$B$2)*--('Gap Analysis data
Pull'!$D$2:$D$362>Matrix!$A6)*--('Gap Analysis data
Pull'!$D$2:$D$362<=Matrix!$B6)*--('Gap Analysis data
Pull'!$D$2:$D$362<>""))/SUMPRODUCT(--('Gap Analysis data
Pull'!$A$2:$A$362<>"")*--('Gap Analysis data
Pull'!$D$2:$D$362<>"")),SUMPRODUCT(--('Gap Analysis data
Pull'!$J$2:$J$362=Matrix!$B$2)*--('Gap Analysis data
Pull'!$I$2:$I$362=Matrix!$B$1)*--('Gap Analysis data
Pull'!$D$2:$D$362>Matrix!$A6)*--('Gap Analysis data
Pull'!$D$2:$D$362<=Matrix!$B6)*--('Gap Analysis data
Pull'!$D$2:$D$362<>""))/SUMPRODUCT(--('Gap Analysis data
Pull'!$A$2:$A$362<>"")*--('Gap Analysis data Pull'!$D$2:$D$362<>"")))
</code>I've attempted to next another IF statement into this formula, which looks like this:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=IF($B$1="Total",SUMPRODUCT(--('Gap Analysis data
Pull'!$J$2:$J$362=Matrix!$B$2)*--('Gap Analysis data
Pull'!$D$2:$D$362>Matrix!$A5)*--('Gap Analysis data
Pull'!$D$2:$D$362<=Matrix!$B5)*--('Gap Analysis data
Pull'!$D$2:$D$362<>""))/SUMPRODUCT(--('Gap Analysis data
Pull'!$A$2:$A$362<>"")*--('Gap Analysis data
Pull'!$D$2:$D$362<>"")),IF($B$2="TOTAL",SUMPRODUCT(--('Gap Analysis data
Pull'!$J$2:$J$362=Matrix!$B$2)*--('Gap Analysis data
Pull'!$D$2:$D$362>Matrix!$A5)*--('Gap Analysis data
Pull'!$D$2:$D$362<=Matrix!$B5)*--('Gap Analysis data
Pull'!$D$2:$D$362<>""))/SUMPRODUCT(--('Gap Analysis data
Pull'!$A$2:$A$362<>"")*--('Gap Analysis data
Pull'!$D$2:$D$362<>"")),SUMPRODUCT(--('Gap Analysis data
Pull'!$J$2:$J$362=Matrix!$B$2)*--('Gap Analysis data
Pull'!$I$2:$I$362=Matrix!$B$1)*--('Gap Analysis data
Pull'!$D$2:$D$362>Matrix!$A5)*--('Gap Analysis data
Pull'!$D$2:$D$362<=Matrix!$B5)*--('Gap Analysis data
Pull'!$D$2:$D$362<>""))/SUMPRODUCT(--('Gap Analysis data
Pull'!$A$2:$A$362<>"")*--('Gap Analysis data Pull'!$D$2:$D$362<>""))))</code>

 

[Oscar Cronquist]

First, you don't need to use minus signs when you multiply arrays. The first formula:

=IF($B$1="Total",SUMPRODUCT(('Gap Analysis data Pull'!$J$2:$J$362=Matrix!$B$2)*('Gap Analysis data Pull'!$D$2:$D$362>Matrix!$A6)*('Gap Analysis data Pull'!$D$2:$D$362<=Matrix!$B6)*('Gap Analysis data Pull'!$D$2:$D$362<>""))/SUMPRODUCT(('Gap Analysis data Pull'!$A$2:$A$362<>"")*('Gap Analysis data Pull'!$D$2:$D$362<>"")),SUMPRODUCT(('Gap Analysis data Pull'!$J$2:$J$362=Matrix!$B$2)*('Gap Analysis data Pull'!$I$2:$I$362=Matrix!$B$1)*('Gap Analysis data Pull'!$D$2:$D$362>Matrix!$A6)*('Gap Analysis data Pull'!$D$2:$D$362<=Matrix!$B6)*('Gap Analysis data Pull'!$D$2:$D$362<>""))/SUMPRODUCT(('Gap Analysis data Pull'!$A$2:$A$362<>"")*('Gap Analysis data Pull'!$D$2:$D$362<>"")))

It is hard to understand what you are looking for, the second formula has nested IF functions, both $B$1 and $B$2 can't be equal to "TOTAL" at the same time since they are nested?

 

[Rewcifer]

Hi Oscar!

Thank you for your reply. As for the minus signs, some of the columns require me to translate text into binary numbers in order to count them, which is why I added the minus signs. Are you saying I don't need them even if some of the columns I'm counting have text in them? I'll remove them if that's the case!

As for what I'm looking for, let me try to explain without jumbling it up again. This workbook is attempting to count and sum up different types of sales based on two lists I've created; that is, one by "type of sale," and the other by "brand." I wanted to add in the function of being able to sum up sales by the total amount of brands and total type of sales within this project. So if you are looking at the workbook, the two custom lists are in B1 and B2, so in a sense, yes, they both can represent total, but just different metrics. Attempting to include that second IF statement to also sum up the brands into a "total" is what I am having difficulty with. Does that make sense? Thank you again for even fielding this query! Let me know if I can offer any more information to illustrate what I'm attempting to do with more clarity.

Best,
A

 

[Aladin Akyurek]

Is $B$1 in IF the same thing as Matrix!$B$1?

 

[Rewcifer]

Yes, assume that B1 and B2 are in the Matrix tab. Unsure why Excel is doing that.

 

[Aladin Akyurek]

Yes, assume that B1 and B2 are in the Matrix tab. Unsure why Excel is doing that.

If so, care to explain what the first IF formula is intended to do, which is made readable by removing the sheet name Matrix, reducing Gap Analysis data Pull to just Gap, and replacing the star syntax with the equivalent comma syntax?

=IF($B$1="Total",
     SUMPRODUCT(--(Gap!$J$2:$J$362=$B$2),--(Gap$D$2:$D$362>$A6),(Gap!$D$2:$D$362<=$B6),--(Gap!$D$2:$D$362<>""))/
     SUMPRODUCT(--(Gap!$A$2:$A$362<>""),--(Gap!$D$2:$D$362<>"")),
       SUMPRODUCT(--(Gap!$J$2:$J$362=$B$2),--(Gap!$I$2:$I$362=$B$1),--(Gap!$D$2:$D$362>$A6),--(Gap!$D$2:$D$362<=$B6),--(Gap!$D$2:$D$362<>""))/
       SUMPRODUCT(--(Gap!$A$2:$A$362<>""),--(Gap!$D$2:$D$362<>"")))

 

[Rewcifer]

Of course!

Let me preface this explanation once more that B1 and B2 are custom lists I made based on two metrics; namely, type of sale and brand. A6 and B6 represent two more restrictions, which are merely price ranges. So, for the sake of an example, this formula would be restricting the search to everything between $3.00 and $6.00.

The first IF formula is basically me telling excel that if the drop down present in B1 says "TOTAL," I want excel to look at B2, see what brand I have chosen, and then run the calculation as such for ALL items, which for this particular formula should return the % of items that are brand X and between $3.00 and $6.00. I want to include a second IF statement that allows the brand list to also be summed up in total, therefore I can present a total view of all brands and all types of sale, as well as different types of sales and different types of brands, which the formula can already perform. Does that make sense? I'm happy to explain again if this isn't sufficient!

 

[Aladin Akyurek]

I think we should settle on this one before proceeding.

Care to list items that can be chosen from B1 and from B2?

 

[Rewcifer]

Hi Aladin,

Thanks for all of your help so far. I unfortunately can't name what items are in B1 and B2 exactly because it's proprietary; however, I've uploaded an image here (https://imgur.com/a/qDrSzc4) of what the lists look like and the following conditions; that is, price bands. Suffice it to say that each list is composed of items that are in text, and not numbers. So, more specifically, "Brand 1," "Brand 2," etc. As for sales type, it would be "X Group," "Y Group," etc. The other metrics to the right of the price bands include % of items, count of items, % of sales, and total sales. Does that give you enough information to understand what is in those lists? Thank you!

 

[Aladin Akyurek]

Please try to create a small fake sample along with the desired results.