Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT PROBLEM | Excel Worksheet Functions | |||
Problem using ADDRESS() in SUMPRODUCT() | Excel Discussion (Misc queries) | |||
Frustrating SUMPRODUCT problem. | Excel Discussion (Misc queries) | |||
SUMPRODUCT Problem | Excel Discussion (Misc queries) | |||
SUMPRODUCT problem | Excel Worksheet Functions |