• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

Sumproduct adding column which also contains text (excel 2003)

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
easycapital
Asked:
easycapital
  • 7
  • 4
  • 3
  • +2
4 Solutions
 
jppintoCommented:
Formula to put on cell F5:

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

jppinto
0
 
jppintoCommented:
Working example attached...
Sumproduct---adding-area-with-te.xls
0
 
easycapitalAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
barry houdiniCommented:
Try like this, JP

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

regards, barry
0
 
easycapitalAuthor Commented:
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
 
jppintoCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
barry houdiniCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
jppintoCommented:
I belive that JP requested "Could the sumproduct be used for the entire column instead"... that's why I presented that formula...
0
 
barry houdiniCommented:
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
 
barry houdiniCommented:
>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
 
jppintoCommented:
Good point Barry... :)
0
 
jppintoCommented:
I'm using Excel 2010 and my formula worked here...didn't tested in Excel 2003.
0
 
Rory ArchibaldCommented:
@jppinto - you can't use entire columns in array formulas (including SUMPRODUCT) in 2003, as Barry said.
0
 
Rory ArchibaldCommented:
Sorry - should have refreshed. :)
0
 
jppintoCommented:
no problem rory...
0
 
easycapitalAuthor Commented:
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

Independent Software Vendors: 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!

  • 7
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now