SUMPRODUCT

  • Thread starter Thread starter Peter B
  • Start date Start date
P

Peter B

Hi all,

I am having a problem with the SUMPRODUCT function. Note
that I am using norwegian settings and the "," is ";" on
my computer.

If I use the formula below I get 0 in value. I know it is
not zero.

=SUMPRODUCT((Data!$B$1:$B$1275='Trading P&L'!$A15);(Data!
$E$1:$E$1275))

If I change the formula seperator to "*" I'll get #Value

=SUMPRODUCT((Data!$B$1:$B$1275='Trading P&L'!$A15)*(Data!
$E$1:$E$1275))

The 'Trading P%L'!$A15 cell is a text that I uses as the
criteria for the sum of the list that I have in the Data
sheet. Does anyone know why this formula does not work. I
have tried to make a dummy list in the same sheet and it
returns the correct number if I uses the "*" sign as a
seperator. If I uses the ";" it returns the value 0.

Rgds,

(e-mail address removed)
 
Peter,

The expression (Data!$B$1:$B$1275='Trading P&L'!$A15)
returns a series of trues and falses. The "*" forces them
into numbers (1 for true and 0 for false) that doesn't happen
when the ";" is used. In order to force them put -- in front

=SUMPRODUCT(--(Data!$B$1:$B$1275='Trading P&L'!$A15);
(Data!$E$1:$E$1275))

You could also use a SUMIF
=SUMIF(Data!$B$1:$B$1275,'Trading P&L'!$A15,
Data!$E$1:$E$1275)

Dan E
 
Back
Top