Sumproduct problem...
 

So I can't get the formula below to work. Any suggestions?

For reference, the sheet with all the data is called "All Movements".
From that sheet I want to sum all the figures in column E that match
with the name "Mike" in column D, and the province abbreviation "AB" in
column O.

=SUMPRODUCT(--('All Movements'!D:D="Mike"),--('All
Movements'!O:O="AB"),'All Movements'!E:E)

Right now I get #NUM error in the cell.

Thanks.

Sumproduct problem...
 

Hi

You can't use whole column references with Sumproduct.
Change to
=SUMPRODUCT(--('All Movements'!$D$1::$D$65535="Mike"),--('All
Movements'!$O$1:$O$65535="AB"),'All Movements'!$E$:$E$65535)


--
Regards

Roger Govier


wrote in message
ups.com...
So I can't get the formula below to work. Any suggestions?

For reference, the sheet with all the data is called "All Movements".
From that sheet I want to sum all the figures in column E that match
with the name "Mike" in column D, and the province abbreviation "AB"
in
column O.

=SUMPRODUCT(--('All Movements'!D:D="Mike"),--('All
Movements'!O:O="AB"),'All Movements'!E:E)

Right now I get #NUM error in the cell.

Thanks.

Sumproduct problem...
 

Apologies
I inadvertently put an extra colon in when modifying your formula. It
should read
=SUMPRODUCT(--('All Movements'!$D$1:$D$65535="Mike"),--('All
Movements'!$O$1:$O$65535="AB"),'All Movements'!$E$:$E$65535)

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi

You can't use whole column references with Sumproduct.
Change to
=SUMPRODUCT(--('All Movements'!$D$1::$D$65535="Mike"),--('All
Movements'!$O$1:$O$65535="AB"),'All Movements'!$E$:$E$65535)


--
Regards

Roger Govier


wrote in message
ups.com...
So I can't get the formula below to work. Any suggestions?

For reference, the sheet with all the data is called "All Movements".
From that sheet I want to sum all the figures in column E that match
with the name "Mike" in column D, and the province abbreviation "AB"
in
column O.

=SUMPRODUCT(--('All Movements'!D:D="Mike"),--('All
Movements'!O:O="AB"),'All Movements'!E:E)

Right now I get #NUM error in the cell.

Thanks.