Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    =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<>"")))
    I've attempted to next another IF statement into this formula, which looks like this:
    =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<>""))))

  2. #2
    Board Regular
    Join Date
    Apr 2018
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?
    Last edited by Oscar Cronquist; May 19th, 2018 at 02:34 PM.

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,153
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    6 Thread(s)

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

    Is $B$1 in IF the same thing as Matrix!$B$1?
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,153
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    6 Thread(s)

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

    Quote Originally Posted by Rewcifer View Post
    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?
    Code:
    =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<>"")))
    
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    May 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,153
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    6 Thread(s)

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

    I think we should settle on this one before proceeding.

    Care to list items that can be chosen from B1 and from B2?
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    May 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,153
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    6 Thread(s)

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

    Please try to create a small fake sample along with the desired results.
    Assuming too much and qualifying too much are two faces of the same problem.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com