|
SumProduct + cells
This formula works:
=SUMPRODUCT((('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="VD")+('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="PR"))*('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$F$13:$F$4000=D9)) I also want to add the sum of other cells (say h50 & h51...) to the above. I can't get a syntax that give me the right answer. In other words: =H50 + H51+=SUMPRODUCT((('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="VD")+('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="PR"))*('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$F$13:$F$4000=D9)) Any ideas? Thanks as always. WAL50 |
SumProduct + cells
The formula should work as written (except that you have a +=
instead of just a + after H51). Do you get an error value, or just an incorrect result? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "wal50" wrote in message ... This formula works: =SUMPRODUCT((('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="VD")+('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="PR"))*('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$F$13:$F$4000=D9)) I also want to add the sum of other cells (say h50 & h51...) to the above. I can't get a syntax that give me the right answer. In other words: =H50 + H51+=SUMPRODUCT((('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="VD")+('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="PR"))*('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$F$13:$F$4000=D9)) Any ideas? Thanks as always. WAL50 |
SumProduct + cells
=SUM(H50:H51,SUMPRODUCT(--ISNUMBER(MATCH('[Retail East Employees
12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000,{"VD","PR"},0)),--([Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$F$13:$F$4000=D9))) wal50 wrote: This formula works: =SUMPRODUCT((('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="VD")+('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="PR"))*('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$F$13:$F$4000=D9)) I also want to add the sum of other cells (say h50 & h51...) to the above. I can't get a syntax that give me the right answer. In other words: =H50 + H51+=SUMPRODUCT((('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="VD")+('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="PR"))*('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$F$13:$F$4000=D9)) Any ideas? Thanks as always. WAL50 |
SumProduct + cells
Thanks. Boy, did I over complicate this one. (incorrect result)
Thanks again WAL50 "Chip Pearson" wrote: The formula should work as written (except that you have a += instead of just a + after H51). Do you get an error value, or just an incorrect result? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "wal50" wrote in message ... This formula works: =SUMPRODUCT((('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="VD")+('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="PR"))*('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$F$13:$F$4000=D9)) I also want to add the sum of other cells (say h50 & h51...) to the above. I can't get a syntax that give me the right answer. In other words: =H50 + H51+=SUMPRODUCT((('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="VD")+('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$D$13:$D$4000="PR"))*('[Retail East Employees 12-31-05.xls]Active Employees Report (As of)'!$F$13:$F$4000=D9)) Any ideas? Thanks as always. WAL50 |
| All times are GMT +1. The time now is 12:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com