How to add 2 more conditions to a sumproduct function

[anita2017]

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

[anita2017]

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

[anita2017]

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

[JeteMc]

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.

[anita2017]

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

Thank you!

Anita

[JeteMc]

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.

[anita2017]

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