I need an excel formula corrected

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?
LVL 1
wrt1meaAsked:
Who is Participating?
 
Saurabh Singh TeotiaConnect With a Mentor Commented:

This should work...

=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 this is not working can you post worksheet...

Saurabh...
0
 
Rory ArchibaldConnect With a Mentor Commented:
Yes:

=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"))
0
 
wrt1meaAuthor Commented:
Both worked! Anyone care to explain what i did wrong?
0
Cloud Class® Course: Microsoft Exchange Server

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.

 
barry houdiniCommented:
I think the parentheses were out as you suggested - in fact you can get rid of some more - this should work

=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!$K$2:$K$10000="N"),Mansfield!$J$2:$J$10000)

regards, barry
0
 
wrt1meaAuthor Commented:
For some reason I always thought that if you opened the formula with 3 parentheses, you had to close it with that number too...
0
 
barry houdiniCommented:
Well, you obviously have to match the parentheses, for every one you open you must close one....but you can still do that correctly and get the formula wrong......

...in this formula (your first one)

=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")

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

=SUMPRODUCT(((Mansfield!$H$2:$H$10000>$P$1)+(Mansfield!$I$2:$I$10000>=$P$1))>0)

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>$P$1)+(Mansfield!$I$2:$I$10000>=$P$1)>0)+0,
((Mansfield!$H$2:$H$10000<=$Q$1)+(Mansfield!$I$2:$I$10000<=$Q$1)>0)+0,
(Mansfield!$K$2:$K$10000="N")+0,
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

0
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.