?
Solved

Sumproduct adding column which also contains text (excel 2003)

Posted on 2011-03-14
18
Medium Priority
?
316 Views
Last Modified: 2012-05-11
Formula in F5 is =SUMPRODUCT( (A:A = F3) * (B:B = F4) * (C:C) )

I also tried:  =SUMPRODUCT( (A:A = F3) * (B:B = F4), (C:C) )

Thanks,
Jp Sumproduct---adding-area-with-te.xls aaa
0
Comment
Question by:easycapital
[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
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35132959
Formula to put on cell F5:

=SUMPRODUCT((A4:A7=F3)*(B4:B7=F4)*(C4:C7))

jppinto
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35132968
Working example attached...
Sumproduct---adding-area-with-te.xls
0
 

Author Comment

by:easycapital
ID: 35132985
Hi jppinto:

I have done it before that the sum area can contain text and the formula is able to ignore those (such as where the header lies.  I just can't remember how I did it.

Thanks,
Jp
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:barry houdini
ID: 35133041
Try like this, JP

=SUMPRODUCT((A4:A7=F3)*(B3:C3=F4),B4:C7)

regards, barry
0
 

Author Comment

by:easycapital
ID: 35133072
Hi Barry,

I am trying to prevent an error in the event another row gets added above row4.  Could the sumproduct be used for the entire column instead, utilizing my example?

Thanks,
JP
0
 
LVL 33

Accepted Solution

by:
jppinto earned 500 total points
ID: 35133104
Use this array formula. Enter as an array formula by pressing CTRL+SHIFT+ENTER.

{=SUM(IF(ISTEXT(C:C),0,(C:C))*(A:A=F3)*(B:B=F4))}

See the working example.

jppinto
Sumproduct---adding-area-with-te.xls
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 500 total points
ID: 35133116
Hello,

if you want to include text in the range to be summed, you better use the comma and -- instead of the *, like

=SUMPRODUCT( --(A1:A10= F3),--(B1:B10= F4),C1:C10)

It's also a good idea not to include whole columns in a Sumproduct. It uses a lot of resources.

cheers, teylyn
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 1000 total points
ID: 35133117
Sorry, ignore previous reply, I misunderstood the query. Jppinto's solution is good....but if you want to allow text in the sum range you need to switch to the "native" syntax of SUMPRODUCT with a comma instead of multiplying the sum range with *, i.e.

=SUMPRODUCT((A4:A7=F3)*(B4:B7=F4),C4:C7)

You can't use the whole column in Excel 2003 (you can in Excel 2007) but you can use all but one cell like

=SUMPRODUCT((A2:A65536=F3)*(B2:B65536=F4),C2:C65536)

Not recommended, though as it will slow down the calculation time

regards, barry
0
 
LVL 50
ID: 35133125
Or

=SUMPRODUCT( (A1:A10= F3)*(B1:B10= F4),C1:C10)

The important bit is that the range containing the text will throw an error if you connect it with *
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35133146
I belive that JP requested "Could the sumproduct be used for the entire column instead"... that's why I presented that formula...
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35133149
BTW, #NUM! error is caused by using whole columns in Excel 2003 - if you had text in the sum range with * syntax then you'd get #VALUE! error.....

barry
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 1000 total points
ID: 35133176
>I belive that JP requested "Could the sumproduct be used for the entire column instead"... that's why I presented that formula...

...but that formula won't work in 2003 - you can't use whole columns in any array formula in Excel 2003 - and if you have 2007 or later you can simply use

=SUMPRODUCT((A:A=F3)*(B:B=F4),C:C)

or much better.....

=SUMIFS(C:C,A:A,F3,B:B,F4)

regards, barry

0
 
LVL 33

Expert Comment

by:jppinto
ID: 35133190
Good point Barry... :)
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35133195
I'm using Excel 2010 and my formula worked here...didn't tested in Excel 2003.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35133206
@jppinto - you can't use entire columns in array formulas (including SUMPRODUCT) in 2003, as Barry said.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35133213
Sorry - should have refreshed. :)
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35133219
no problem rory...
0
 

Author Closing Comment

by:easycapital
ID: 35133354
I was aware about the text being an issue, but was not getting the solution.

Barry,

Did not know I could not do an entire column; thanks Barry.  

The sumif does not seem to slow the calc time.

Thank you all for the input on this one.

JP
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

752 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