|
Sumproduct Function Question
Hi All,
This function works: =SUMPRODUCT(AND(($H$9<"x"),($C$9+30<TODAY()))*($D $9=D77),$E$9) But when I update it to this one, it gives a $0 result and there should be a number. =SUMPRODUCT(AND(($H$6:$H$66<"x"),($C$6:$C$66+30<T ODAY()))*($D$6:$D $66=D77),$E$6:$E$66) or {=SUMPRODUCT(AND(($H$6:$H$66<"x"),($C$6:$C$66+30< TODAY()))*($D$6:$D $66=D77),$E$6:$E$66)} Any ideas would be much appreciated? Michele |
Sumproduct Function Question
And() and Or() don't like arrays much. From what I can tell of the formula,
you're trying to sum column E when the following criteria are met: column H is not equal to "x" column c + 30 is less than today's date column d = the value in d77 If that's the case, it should work without the AND function like so: =sumproduct(--($H$6:$H$66<"x"),--($C$6:$C$66+30<today()),--($D$6:$D$66=d77),$E$6:$E$66) If I misinterpreted anything in there, please let me know. -- Mike Lee McKinney,TX USA "mjones" wrote: Hi All, This function works: =SUMPRODUCT(AND(($H$9<"x"),($C$9+30<TODAY()))*($D $9=D77),$E$9) But when I update it to this one, it gives a $0 result and there should be a number. =SUMPRODUCT(AND(($H$6:$H$66<"x"),($C$6:$C$66+30<T ODAY()))*($D$6:$D $66=D77),$E$6:$E$66) or {=SUMPRODUCT(AND(($H$6:$H$66<"x"),($C$6:$C$66+30< TODAY()))*($D$6:$D $66=D77),$E$6:$E$66)} Any ideas would be much appreciated? Michele |
Sumproduct Function Question
Hi,
I tried it as you suggested: {=SUMPRODUCT(($H$6:$H$66<"x"),($C$6:$C$66+30<TODA Y()),($D$6:$D $66=D77),$E$6:$E$66)} I'm not sure why you put in the dashes. But it still comes out as zero. Your understanding of what I'm trying to accomplish is accurate. Maybe it's to do with the cell formats. They a C - 1/18/2007 formated as Date 18-Jan D - general for company names E - custom - _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_($* "-"?? _)_);_(_(@_)_) G - custom - _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_(* ""?? _)_);_(_(@_)_) - this is where the formula is H - general - this is where the x will be to indicate paid or blank if not paid Any other ideas would be greatly appreciated. Thanks, Michele ----------- On Mar 14, 4:57 pm, mikelee101 <mikelee101athotmaildotcom wrote: And() and Or() don't like arrays much. From what I can tell of the formula, you're trying to sum column E when the following criteria are met: column H is not equal to "x" column c + 30 is less than today's date column d = the value in d77 If that's the case, it should work without the AND function like so: =sumproduct(--($H$6:$H$66<"x"),--($C$6:$C$66+30<today()),--($D$6:$D$66=d77*),$E$6:$E$66) If I misinterpreted anything in there, please let me know. -- Mike Lee McKinney,TX USA "mjones" wrote: Hi All, This function works: =SUMPRODUCT(AND(($H$9<"x"),($C$9+30<TODAY()))*($D $9=D77),$E$9) But when I update it to this one, it gives a $0 result and there should be a number. =SUMPRODUCT(AND(($H$6:$H$66<"x"),($C$6:$C$66+30<T ODAY()))*($D$6:$D $66=D77),$E$6:$E$66) or {=SUMPRODUCT(AND(($H$6:$H$66<"x"),($C$6:$C$66+30< TODAY()))*($D$6:$D $66=D77),$E$6:$E$66)} Any ideas would be much appreciated? Michele- Hide quoted text - - Show quoted text - |
Sumproduct Function Question
Hi
It is not an array entered formula so you don't need to enter with Control+Shift+Enter. Just Enter will do. There will not be any curly braces { } around the formula. You do need the double unary minus to coerce the tests of True or False to 1 or 0, if you are going to uses a comma between the expressions. Your test for dates will not work as you have expressed it, turn it round as shown in the formula below to take the 30 days away from today, rather than trying to add it to the dates in C6:C66 =SUMPRODUCT(--($H$6:$H$66<"x"),--($C$6:$C$66<TODAY()-30), --($D$6:$D$66=D77),$E$6:$E$66) Alternatively, without the double unary minus =SUMPRODUCT(($H$6:$H$66<"x")*($C$6:$C$66<TODAY()-30)* ($D$6:$D$66=D77)*$E$6:$E$66) In this case the multiplication between expressions does the coercion for True and False to 1 and 0 -- Regards Roger Govier "mjones" wrote in message ups.com... Hi, I tried it as you suggested: {=SUMPRODUCT(($H$6:$H$66<"x"),($C$6:$C$66+30<TODA Y()),($D$6:$D $66=D77),$E$6:$E$66)} I'm not sure why you put in the dashes. But it still comes out as zero. Your understanding of what I'm trying to accomplish is accurate. Maybe it's to do with the cell formats. They a C - 1/18/2007 formated as Date 18-Jan D - general for company names E - custom - _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_($* "-"?? _)_);_(_(@_)_) G - custom - _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_(* ""?? _)_);_(_(@_)_) - this is where the formula is H - general - this is where the x will be to indicate paid or blank if not paid Any other ideas would be greatly appreciated. Thanks, Michele ----------- On Mar 14, 4:57 pm, mikelee101 <mikelee101athotmaildotcom wrote: And() and Or() don't like arrays much. From what I can tell of the formula, you're trying to sum column E when the following criteria are met: column H is not equal to "x" column c + 30 is less than today's date column d = the value in d77 If that's the case, it should work without the AND function like so: =sumproduct(--($H$6:$H$66<"x"),--($C$6:$C$66+30<today()),--($D$6:$D$66=d77*),$E$6:$E$66) If I misinterpreted anything in there, please let me know. -- Mike Lee McKinney,TX USA "mjones" wrote: Hi All, This function works: =SUMPRODUCT(AND(($H$9<"x"),($C$9+30<TODAY()))*($D $9=D77),$E$9) But when I update it to this one, it gives a $0 result and there should be a number. =SUMPRODUCT(AND(($H$6:$H$66<"x"),($C$6:$C$66+30<T ODAY()))*($D$6:$D $66=D77),$E$6:$E$66) or {=SUMPRODUCT(AND(($H$6:$H$66<"x"),($C$6:$C$66+30< TODAY()))*($D$6:$D $66=D77),$E$6:$E$66)} Any ideas would be much appreciated? Michele- Hide quoted text - - Show quoted text - |
Sumproduct Function Question
Great, that worked. Your really smart. Thanks. ---------- On Mar 14, 6:10 pm, "Roger Govier" wrote: Hi It is not an array entered formula so you don't need to enter with Control+Shift+Enter. Just Enter will do. There will not be any curly braces { } around the formula. You do need the double unary minus to coerce the tests of True or False to 1 or 0, if you are going to uses a comma between the expressions. Your test for dates will not work as you have expressed it, turn it round as shown in the formula below to take the 30 days away from today, rather than trying to add it to the dates in C6:C66 =SUMPRODUCT(--($H$6:$H$66<"x"),--($C$6:$C$66<TODAY()-30), --($D$6:$D$66=D77),$E$6:$E$66) Alternatively, without the double unary minus =SUMPRODUCT(($H$6:$H$66<"x")*($C$6:$C$66<TODAY()-30)* ($D$6:$D$66=D77)*$E$6:$E$66) In this case the multiplication between expressions does the coercion for True and False to 1 and 0 -- Regards Roger Govier "mjones" wrote in message ups.com... Hi, I tried it as you suggested: {=SUMPRODUCT(($H$6:$H$66<"x"),($C$6:$C$66+30<TODA Y()),($D$6:$D $66=D77),$E$6:$E$66)} I'm not sure why you put in the dashes. But it still comes out as zero. Your understanding of what I'm trying to accomplish is accurate. Maybe it's to do with the cell formats. They a C - 1/18/2007 formated as Date 18-Jan D - general for company names E - custom - _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_($* "-"?? _)_);_(_(@_)_) G - custom - _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_(* ""?? _)_);_(_(@_)_) - this is where the formula is H - general - this is where the x will be to indicate paid or blank if not paid Any other ideas would be greatly appreciated. Thanks, Michele ----------- On Mar 14, 4:57 pm, mikelee101 <mikelee101athotmaildotcom wrote: And() and Or() don't like arrays much. From what I can tell of the formula, you're trying to sum column E when the following criteria are met: column H is not equal to "x" column c + 30 is less than today's date column d = the value in d77 If that's the case, it should work without the AND function like so: =sumproduct(--($H$6:$H$66<"x"),--($C$6:$C$66+30<today()),--($D$6:$D$66=d77**),$E$6:$E$66) If I misinterpreted anything in there, please let me know. -- Mike Lee McKinney,TX USA "mjones" wrote: Hi All, This function works: =SUMPRODUCT(AND(($H$9<"x"),($C$9+30<TODAY()))*($D $9=D77),$E$9) But when I update it to this one, it gives a $0 result and there should be a number. =SUMPRODUCT(AND(($H$6:$H$66<"x"),($C$6:$C$66+30<T ODAY()))*($D$6:$D $66=D77),$E$6:$E$66) or {=SUMPRODUCT(AND(($H$6:$H$66<"x"),($C$6:$C$66+30< TODAY()))*($D$6:$D $66=D77),$E$6:$E$66)} Any ideas would be much appreciated? Michele- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 09:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com