# 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
###### Who is Participating?

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

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

Author Commented:
Both worked! Anyone care to explain what i did wrong?
0

Commented:
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

Author 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

Commented:
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.