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>

 

[Rewcifer]

See link here: https://we.tl/aQRVcrc5E0

This is the workbook with mock data in it!

 

[Aladin Akyurek]

See link here: https://we.tl/aQRVcrc5E0

This is the workbook with mock data in it!

Choosing for TOTAL in B1 of Matrix means all of the price groups, right?

And, choosing TOTAL in B2 of Matrix means all of the brands, right?

 

[Rewcifer]

Correct! I'm trying to create another IF statement that will sum up the total of all price groups and all brands so long as both lists say "TOTAL."

 

[Aladin Akyurek]

Modify the group list in Lists as follows so that it's in line with the data.

Book1
	B
3	Total
4	Group 1
5	Group 2
6	Group 3
7	Group 4
8	Group 5
Lists

The formula that you need in column D of Matrix for Count of SKU's is:

In D5 enter and copy down...

=COUNTIFS('Data Pull'!$I$2:$I$362,IF($B$1="total","?*",$B$1),'Data Pull'!$J$2:$J$362,IF($B$2="total","?*",$B$2),'Data Pull'!$D$2:$D$362,">"&$A5,'Data Pull'!$D$2:$D$362,"<="&$B5,'Data Pull'!$D$2:$D$362,"<>")

and in column F of Matrix for Sales is:

In F5 enter and copy down...

=SUMIFS('Data Pull'!$K$2:$K$362,'Data Pull'!$I$2:$I$362,IF($B$1="total","?*",$B$1),'Data Pull'!$J$2:$J$362,IF($B$2="total","?*",$B$2),'Data Pull'!$D$2:$D$362,">"&$A5,'Data Pull'!$D$2:$D$362,"<="&$B5,'Data Pull'!$D$2:$D$362,"<>")

I thrust you can set up the rest yourself.

 

[Rewcifer]

Thank you so much, Aladin! May I ask one more question? More specifically, how does the logic of the "?*" work in the part of the formula below?

IF($B$1="total","?*",$B$1)

Thank you!

 

[Aladin Akyurek]

The wildcard ?* combination means any text consisting of char or more. When B1 = Total, we accept everything in the range to which this combination applies, otherwise just those which equal B1.

 

[Rewcifer]

Understood. Thank you for your help, Aladin!

 

[Aladin Akyurek]

Understood. Thank you for your help, Aladin!

You are very welcome.