# Problem with SUMPRODUCT Formula

Dear Experts,
I'm using in lot's of excel files the SUMPRODUCT formula. I have to admit that this was all time in 2003 versions, now I'm setting it up with a 2007 excel version and I get only #Value as answer.
What is wrong here? I'm blind? Or something should be different in 2007 english version? I tried to replace * with ; but it did not help.
\$B\$2 is for example: Xerox Phaser 5335
\$A5 is for example: NIK (question: Does it matter if it is NIK or nik? If I have in the Data-Import nik. How I have to modify the formula so nik will be counted as NIK?
MONTH is giving me a 2 and I have a 2 for Feb in that field...
The E column are figures like 124 and 2 and 5 and the idea of the formula is:
Count all pages together from all cells iF printer in G column is like printer state in B cell and if nickname from D column is like nickname in A cell and if date in C column contains month 2.

=SUMPRODUCT(('Data-Import'!\$G\$2:\$G\$10000=\$B\$2)*('Data-Import'!\$D\$2:\$D\$10000)=\$A5)*(MONTH('Data-Import'!\$C\$2:\$C\$10000)=C\$4)*Data-Import!\$E\$2:\$E\$10000

thanks
Nils
###### Who is Participating?

Commented:
...OK and comma in english should be ; in your version so that would be:

=SUMPRODUCT(('Data-Import'!\$G\$2:\$G\$10000=\$B\$2)*('Data-Import'!\$D\$2:\$D\$10000=\$A5)*(MONTH('Data-Import'!\$C\$2:\$C\$10000)=C\$4);Data-Import!\$E\$2:\$E\$10000)

regards, barry
0

Commented:
Hello Nils, looks like you have some parentheses wrong in that version, is that a typo here or in your actual formula? try like this, using comma rather than * for last separator could avoid #VALUE! error......

=SUMPRODUCT(('Data-Import'!\$G\$2:\$G\$10000=\$B\$2)*('Data-Import'!\$D\$2:\$D\$10000=\$A5)*(MONTH('Data-Import'!\$C\$2:\$C\$10000)=C\$4),Data-Import!\$E\$2:\$E\$10000)

regards, barry

0

Commented:
.If I have in the Data-Import nik. How I have to modify the formula so nik will be counted as NIK?

For NIK try:

=SUMPRODUCT(('Data-Import'!\$G\$2:\$G\$10000=\$B\$2)*('Data-Import'!\$D\$2:\$D\$10000=\$A5)*(MONTHUPPER(('Data-Import'!\$C\$2:\$C\$10000)=C\$4);Data-Import!\$E\$2:\$E\$10000)
)
For nik, try:
=SUMPRODUCT(('Data-LOWER(Import'!\$G\$2:\$G\$10000)=\$B\$2)=\$A5)*(MONTh LOWER(('Data-Import'!\$C\$2:\$C\$10000)=C\$4);Data-Import!\$E\$2:\$E\$10000)
)

0

Commented:
Patrick,

0

Author Commented:
Hi, thanks for quick respond and help. Looks like it was simply wrong parentheses. All works fine now, no matter if NIK capitalized or not!
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.