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.
Please see my formula:
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.


Who is Participating?
barry houdiniConnect With a Mentor Commented:
...OK and comma in english should be ; in your version so that would be:


regards, barry
barry houdiniCommented:
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......


regards, barry

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

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)

Sorry to hijack this thread, but I sent you an email to your secondary address.

Petersburg1Author 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!
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.