Sum with multiple conditions
I'm trying to write a sumproduct formula with mulitple conditions:
C6=4/25/08 C44=Project121 I'm using: =SUMPRODUCT(--(Sheet1!K$2:K$50001<=--C$6),--(Sheet1!E$2:E$50001=C44),Sheet1!T$2:T$50001) I want to sum the values in column T with the specific data in C44 athat has a date less than 4/25/08. The data in column C changes, so C43 could be Project151. Also I want the date to alway be the current date, that is why I'm refrencing it to a specific cell, using =today(). I keep getting a #ref! error. The previous formula I was using was working but it didn't factor in the date. =SUMIF(Sheet1!$E$2:$E$50001,C44,Sheet1!T$2:T$50001 ) Can anyone help? Using Excel2003 |
Sum with multiple conditions
=SUMPRODUCT(--(Sheet1!K$2:K$50001<=--C$6),--(Sheet1!E$2:E$50001=C44),Sheet1!T$2:T$50001)
If C6 is a true Excel date then you don't need the double unary: --C$6. If C6 is a TEXT string that looks like a date then the double unary will coerce the string to a true Excel date. Other than that, there's nothing wrong with the formula. If Sheet1 doesn't exist then you'll get a #REF! error. Or, do you have #REF! errors already in any of the ranges? -- Biff Microsoft Excel MVP "John" wrote in message ... I'm trying to write a sumproduct formula with mulitple conditions: C6=4/25/08 C44=Project121 I'm using: =SUMPRODUCT(--(Sheet1!K$2:K$50001<=--C$6),--(Sheet1!E$2:E$50001=C44),Sheet1!T$2:T$50001) I want to sum the values in column T with the specific data in C44 athat has a date less than 4/25/08. The data in column C changes, so C43 could be Project151. Also I want the date to alway be the current date, that is why I'm refrencing it to a specific cell, using =today(). I keep getting a #ref! error. The previous formula I was using was working but it didn't factor in the date. =SUMIF(Sheet1!$E$2:$E$50001,C44,Sheet1!T$2:T$50001 ) Can anyone help? Using Excel2003 |
All times are GMT +1. The time now is 03:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com