Sumproduct problem...

  • Thread starter Thread starter cjwenngatz
  • Start date Start date
C

cjwenngatz

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
Click to expand...
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.
 
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)
 
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)
 
Back
Top