The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

When I am trying to use the following formula, I get (0) returned and I know there should be a large number:

=SUMPRODUCT(((Mansfield!$H$2:$H$10000>$P$1)+(Mansfield!$I$2:$I$10000>=$P$1))>0)*(((Mansfield!$H$2:$H$10000<=$Q$1)+(Mansfield!$I$2:$I$10000<=$Q$1))>0)*(Mansfield!$J$2:$J$10000)*(Mansfield!$K$2:$K$10000="N")

If I use this one, I get an anwser:

=SUMPRODUCT((Mansfield!$C$2:$C$10000=DATA!A5)*(((Mansfield!$H$2:$H$10000>$P$1)+(Mansfield!$I$2:$I$10000>=$P$1))>0)*(((Mansfield!$H$2:$H$10000<=$Q$1)+(Mansfield!$I$2:$I$10000<=$Q$1))>0)*(Mansfield!$J$2:$J$10000)*(Mansfield!$K$2:$K$10000="N"))

The only difference is ((Mansfield!$C$2:$C$10000=DATA!A5), which is a column and reference for a persons name. I am wanting the total for the values and am not looking for a specific name. Do I have some parenthesis out of whack?

=SUMPRODUCT(((Mansfield!$H

If I use this one, I get an anwser:

=SUMPRODUCT((Mansfield!$C$

The only difference is ((Mansfield!$C$2:$C$10000=

=SUMPRODUCT((((Mansfield!$

=SUMPRODUCT(((Mansfield!$H

regards, barry

...in this formula (your first one)

=SUMPRODUCT(((Mansfield!$H

.....you are missing a parenthesis at the start so in fact the SUMPRODUCT ends too early.......it's only this part

=SUMPRODUCT(((Mansfield!$H

because by that point you have closed as many parentheses as you have opened, so SUMPRODUCT is finished....and in the above there is no "co-ercer" [you aren't multiplying by anything or using *1 etc,] so it will always give a result of zero....then the rest of the formula will be irrelevant because whatever it is, you are multiplying by zero...so the result is going to be zero. Perhaps simpler to write like this

=SUMPRODUCT(

((Mansfield!$H$2:$H$10000>

((Mansfield!$H$2:$H$10000<

(Mansfield!$K$2:$K$10000="

Mansfield!$J$2:$J$10000

)

The function name and the opening bracket is on line 1 and the last line is just the closing bracket. Lines 2 , 3 and 4 are individula conditions and in each of those the brackets must match, line 5 is the sum range which doesn't need any.....

regards, barry

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

This should work...

=SUMPRODUCT((((Mansfield!$

If this is not working can you post worksheet...

Saurabh...