PC Review


Reply
Thread Tools Rate Thread

Sumproduct + Excel 2003

 
 
New Member
AhujaA's Avatar
Join Date: May 2012
Location: London
Posts: 21
 
      14th May 2012
Hi,

Im trying to use sumproduct formula subject to meeting two conditions, but the data values may contain some #VALUE! values. Because of this my final value is giving #VALUE! Error. Any suggestions how to tackle it?

For example:
a 1990 2

a 1991 5

a 1992 3

a 1990 #VALUE!

a 1991 6

a 1992 7

a 1990 8

a 1991 15

a 1992 12



I want to add where column A is “a”, Column B is “1990” and shud get 10 as result for this condition and not the #VALUE! error.

Any suggetions plz?

Last edited by AhujaA; 14th May 2012 at 11:53 AM.. Reason: formatting better
 
Reply With Quote
 
 
 
 
Excel Formulator
alow's Avatar
Join Date: Mar 2012
Location: US
Posts: 756
 
      14th May 2012
Which version of Excel are you using? If it's 2007 or later, you can use a SUMIFS formula instead, although I don't think that will fix the problem with the #VALUE! errors. Why are you gettings those? I think it would be easier to set up an IF formula in the column with the numbers to hide those with a 0 to make the whole thing simpler.
 
Reply With Quote
 
 
 
 
Excel Formulator
alow's Avatar
Join Date: Mar 2012
Location: US
Posts: 756
 
      14th May 2012
Ha, nevermind the version question, it's in the title. But my recommendation to make things simpler is still to get rid of the #VALUE! errors first.
 
Reply With Quote
 
New Member
AhujaA's Avatar
Join Date: May 2012
Location: London
Posts: 21
 
      14th May 2012
Im using excel 2003 . I cant get rid of #VALUE! cells as these files are coming from some other department.I cant modify the formulas or links to these cells or any other files.

Last edited by AhujaA; 14th May 2012 at 02:05 PM.. Reason: Further explanation
 
Reply With Quote
 
Excel Formulator
alow's Avatar
Join Date: Mar 2012
Location: US
Posts: 756
 
      14th May 2012
All you would need to do is add a column D that has =if(iserror(c2),0,C2) and do the sumproduct on that column instead. If that still doesn't work, I can think about any way to put error trapping in the sum product, but I can almost guarantee a mess of a formula.
 
Reply With Quote
 
New Member
AhujaA's Avatar
Join Date: May 2012
Location: London
Posts: 21
 
      14th May 2012
I know that. but I have given here just an example.. there are lot many other sheets that have this error value at place coming from the very source file. Not possible to update all the linking files for n no. of classes. Is there any other possible way you can think of? thanks.
 
Reply With Quote
 
Excel Formulator
alow's Avatar
Join Date: Mar 2012
Location: US
Posts: 756
 
      14th May 2012
Alright, here's the slop, but it's an array formula. You will have to make sure that when you enter it, you use Ctrl+Shift+Enter and any time you edit it, you hit Ctrl+Shift+Enter:

=SUM((((A1:A100)="a")*((B1:B100)="1990")*(IF(ISERROR(C1:C100),0,C1:C100)))

When you enter this with Ctrl+Shift+Enter, it should put curly brackets ({}) around it. Make sure to the change the ranges accordingly. Also, if your years (1990) are text, leave the quotes. If they are numeric values, take the quotes away from that value. Let me know if that does it.
 
Reply With Quote
 
New Member
AhujaA's Avatar
Join Date: May 2012
Location: London
Posts: 21
 
      14th May 2012
My first condition will be a fixed name, year will be changing from 1993 to 2001 (which i can refer it to the cell), I tried using iserror for the sum range, but it gave me wrong sum of totals, dunno why!
 
Reply With Quote
 
Excel Formulator
alow's Avatar
Join Date: Mar 2012
Location: US
Posts: 756
 
      14th May 2012
Are you using Ctrl+Shift+Enter instead of Enter to put the formula in?
 
Reply With Quote
 
New Member
AhujaA's Avatar
Join Date: May 2012
Location: London
Posts: 21
 
      14th May 2012
Tried this formula :
=SUM(((D6779)="M")*((B6:B779)="1993")*(IF(ISERROR(F6:F779),0,F6:F779)))
WITH CONTROL+SHIFT+ENTER BUT GIVING AN ERROR
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct instead of SumifS in VBA (application.sumproduct) Majken Bilslev-Jensen Microsoft Excel Programming 7 30th Dec 2010 06:56 PM
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE gholly Microsoft Excel Misc 2 28th Sep 2009 05:07 PM
Conditional SUMPRODUCT or SUMPRODUCT with Filters Ted M H Microsoft Excel Worksheet Functions 4 14th Aug 2008 07:50 PM
sumproduct? sumif(sumproduct)? =?Utf-8?B?RGF2aWQ=?= Microsoft Excel Worksheet Functions 3 13th Jul 2007 07:06 PM
SUMPRODUCT - NO SUMPRODUCT! =?Utf-8?B?TWFyaw==?= Microsoft Excel Programming 3 23rd Nov 2005 03:36 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:13 PM.