Solved

Problem with SUMPRODUCT Formula

Posted on 2011-03-10
5
385 Views
Last Modified: 2012-05-11
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.

=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
Comment
Question by:Petersburg1
5 Comments
 
LVL 50

Expert Comment

by:barry houdini
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

by:
barry houdini earned 250 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

by:patrickab
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 80

Expert Comment

by:byundt
ID: 35102555
Patrick,
Sorry to hijack this thread, but I sent you an email to your secondary address.

Brad
0
 

Author Comment

by:Petersburg1
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now