Sum Values on a condition

[Biff]

Take your pick:

=SUMIF(A1:A7,">0",B1:B7)

=SUMPRODUCT(--(A1:A7>0),--B1:B7)

=SUM(IF(A1:A7>0,B1:B7,0)) entered as an array -
CTRL+SHIFT+ENTER

Biff

-----Original Message-----
I have column A with date values. Some of them are blank.
Column B has currency values in it.

Column A Column B
1 jan 04 10.00
10 jan 04 20.00
11 jan 04
15 jan 04
20 jan 04 30.00


How can I sum the values in a different cell for the

values in column B only if there was a corresponding date
value in column A. Cell C1 should read 60.00.

 

[John Michl]

What do the dashes in the sumproduct formula do?

 

[Biff]

Hi John!

What do the dashes in the sumproduct formula do?

The "--" "convert" the Boolean values True/False to 1,0.
This eliminates a cycle in the calculation and makes it a
little faster. Actually, that formula can be written as:

=SUMPRODUCT(--(A1:A7>0),B1:B7)

or:

=SUMPRODUCT((A1:A7>0)*B1:B7)

To see how the "--" affect the formula, enter some test
data in a ws and use both formula syntax. Select the cell
with the formula and use the menu command Evaluate
Formula. Watch the process as the formula resolves.

Biff