Sum column based upon corresponding values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan-X ! - 07 Nov 2006 20:12 GMT

I know this is probably simple but I can't get anything to work. I
have a formula that I took from another document to use in a new
project. The formula looks at the values of a specific range in
another workbook and then counts the values of a corresponding column.
However, I want to change this formula so that it sums the values
instead of counting.

=SUMPRODUCT((QM03_template.xls!$A$2:$A$2000={"FEMA Manual
Determination"})*(QM03_template.xls!$E$2:$E$2000<>""))

Reply to this Message

Bernard Liengme - 07 Nov 2006 20:26 GMT

What column to SUM, I will assume F
=SUMPRODUCT((QM03_template.xls!$A$2:$A$2000={"FEMA Manual

> Determination"})*(QM03_template.xls!$E$2:$E$2000<>"")*QM03_template.xls!$F$2:$F$2000))

Why the {} in ={"FEMA Manual Determination"}

For more on SUMPRODUCT visit
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

>I know this is probably simple but I can't get anything to work. I
> have a formula that I took from another document to use in a new
[quoted text clipped - 5 lines]
> =SUMPRODUCT((QM03_template.xls!$A$2:$A$2000={"FEMA Manual
> Determination"})*(QM03_template.xls!$E$2:$E$2000<>""))

Reply to this Message

Earl Kiosterud - 07 Nov 2006 20:27 GMT

Dan,

Your formula counts rows with "FEMA..." in column A and are nonblank in
column E. If the values to be summed are in column E, try:

=SUMPRODUCT((QM03_template.xls!$A$2:$A$2000={"FEMA Manual
Determination"})*(QM03_template.xls!$E$2:$E$2000))

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

Reply to this Message