Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Problem with SUMPRODUCT Formula

Posted on 2011-03-10
Medium Priority
393 Views
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
0
Question by:Petersburg1
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Expert Comment

ID: 35094708
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

LVL 50

Accepted Solution

barry houdini earned 1000 total points
ID: 35094726
...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

LVL 45

Expert Comment

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

LVL 81

Expert Comment

ID: 35102555
Patrick,

0

Author Comment

ID: 35106012
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
###### Suggested Courses
Course of the Month7 days, 15 hours left to enroll