x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 336

# 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,
0
easycapital
• 7
• 4
• 3
• +2
4 Solutions

Commented:
Formula to put on cell F5:

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

jppinto
0

Commented:
Working example attached...
0

Author 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

Commented:
Try like this, JP

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

regards, barry
0

Author 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

Commented:
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
0

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

Commented:
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

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

Commented:
I belive that JP requested "Could the sumproduct be used for the entire column instead"... that's why I presented that formula...
0

Commented:
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

Commented:
>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

Commented:
Good point Barry... :)
0

Commented:
I'm using Excel 2010 and my formula worked here...didn't tested in Excel 2003.
0

Commented:
@jppinto - you can't use entire columns in array formulas (including SUMPRODUCT) in 2003, as Barry said.
0

Commented:
Sorry - should have refreshed. :)
0

Commented:
no problem rory...
0

Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.