+ Reply to Thread
Results 1 to 7 of 7

How to add 2 more conditions to a sumproduct function

  1. #1
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    64

    How to add 2 more conditions to a sumproduct function

    Dear excel experts,

    Lately, I've learned from the answers to my previous posts of the sumproduct function, and so therefore, I'd like to continue using it. My problem is, I'm struggling with adding another 2 conditions to my prexisiting formula because I do not know where to put the parentheses so that these two conditions are added.

    In the attached excel, I've managed to write out this entire formula =SUMPRODUCT((((($B$2:$B$121="Yes")*($F$2:$F$121=4)*((($C$2:$C$121=1)*(LEN($D$2:$D$121)>0)*($D$2:$D$121<>6)) +(((($C$2:$C$121=2)* (LEN($D$2:$D$121)>=3)*(MID($BJ$2:$BJ$121,3,1)<>"6"))))))))), and if you see in my excel sheet, the answer that I got is 119. This is correct so foar. However, I need to add two more conditions to this formula by only counting the cases whose scores are >=0 and <=10 (located in column G of my excel attachmenet), and therefore, my count should now go down to 116. How do I do that? I think I lose control of where to put the parentheses after so many conditions.


    I'd appreciate any instructions you can provide. Thank you so much for your time!

    Anita
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    64

    Re: How to add 2 more conditions to a sumproduct function

    Oh sorry... I should have said that the final answer, which select cases that have scores between 0 and 10, should be 3 as there were 3 cases with scores of 0s.

    Thank you.

    Anita

  3. #3
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    64

    Re: How to add 2 more conditions to a sumproduct function

    Hi all,

    If you think there's a different way of going around the sumproduct function, please let me know too. I've been struggling for days and at this point, any solution to get to the answer would be greatly appreciated.

    Thank you!

    Anita

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,810

    Re: How to add 2 more conditions to a sumproduct function

    Based on the values in columns B:F the following formula will yield 3: =SUMPRODUCT(($B$2:$B$121="Yes")*($F$2:$F$121=4)*($C$2:$C$121<=2)*(LEN($D$2:$D$121)>0)*($D$2:$D$121<>6)*(G$2:G$121<=10)*(G$2:G$121>=0))
    With the references to column G removed the formula would still yield 119 so I feel as if either there is more to the data set up then we are seeing or that there are unnecessary sections in the original formula.
    If the former is true please show us some examples where the (MID($BJ$2:$BJ$121,3,1)<>"6")) or any of the other omitted sections would be necessary.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    64

    Re: How to add 2 more conditions to a sumproduct function

    Your formula works! And it looks much more simple than mine .

    Thank you!

    Anita

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,810

    Re: How to add 2 more conditions to a sumproduct function

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

  7. #7
    Registered User
    Join Date
    03-04-2017
    Location
    NJ
    MS-Off Ver
    Excel for Mac Version 15.31
    Posts
    64

    Re: How to add 2 more conditions to a sumproduct function

    Oh of course! I thought I had marked the thread as solved already. Here we go... doing it now.

    Thank you again JeteMc for your time and assistance.

    Anita

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 02-20-2018, 10:02 AM
  2. Sumproduct with certain conditions
    By geliedee in forum Excel General
    Replies: 3
    Last Post: 10-26-2015, 05:18 AM
  3. SUMPRODUCT with conditions
    By Steve_Courts in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-24-2013, 05:33 AM
  4. [SOLVED] Help with sumproduct with two conditions
    By pstewart in forum Excel General
    Replies: 1
    Last Post: 08-21-2012, 03:24 PM
  5. sumproduct with 2 conditions
    By tek9step in forum Excel General
    Replies: 4
    Last Post: 02-16-2011, 09:37 AM
  6. Sumproduct with conditions?
    By voraciousV in forum Excel General
    Replies: 1
    Last Post: 02-07-2009, 02:33 AM
  7. [SOLVED] sumproduct three conditions
    By Scire in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-09-2006, 01:25 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1