# How to add 2 more conditions to a sumproduct function

1. ## 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  Register To Reply

2. ## 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  Register To Reply

3. ## 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  Register To Reply

4. ## 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.  Register To Reply

5. ## 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  Register To Reply

6. ## 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.  Register To Reply

7. ## 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  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### 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