|
I have never been able to get a SumProduct formula to work..Help!
I have never been able to get a SumProduct formula to work..
Now I Must Have it but the following is not filtering any of the limits and gives me the sum of the entire 'S' column. B3 and C3 are 3 character text fields. Aging!A3 is a date formatted field as is column I on the Funding_Sub-Ledger sheet. SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3)--('Funding_Sub-Ledger'!$O$5:$O$346=C3)--((Aging!$A$3 -'Funding_Sub-Ledger'!$I$5:$I$346)<=30)--('Funding_Sub-Ledger'!$S$5:$S$3460),('Funding_Sub-Ledger'!$S$5:$S$346)) I have tried using '*' in place of '--' as well as all the other formats presented at: http://www.xldynamic.com/source/xld.SUMPRODUCT.html I really need help here! As I said I have never gotten SumProduct to work !! thanks BAC |
I have never been able to get a SumProduct formula to work..Help!
You're missing the argument delimiters and you had an extra set of ( ):
SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3),--('Funding_Sub-Ledger'!$O$5:$O$346=C3),--(Aging!$A$3-'Funding_Sub-Ledger'!$I$5:$I$346<=30),--('Funding_Sub-Ledger'!$S$5:$S$3460),'Funding_Sub-Ledger'!$S$5:$S$346) -- Biff Microsoft Excel MVP "BAC" wrote in message ... I have never been able to get a SumProduct formula to work.. Now I Must Have it but the following is not filtering any of the limits and gives me the sum of the entire 'S' column. B3 and C3 are 3 character text fields. Aging!A3 is a date formatted field as is column I on the Funding_Sub-Ledger sheet. SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3)--('Funding_Sub-Ledger'!$O$5:$O$346=C3)--((Aging!$A$3 -'Funding_Sub-Ledger'!$I$5:$I$346)<=30)--('Funding_Sub-Ledger'!$S$5:$S$3460),('Funding_Sub-Ledger'!$S$5:$S$346)) I have tried using '*' in place of '--' as well as all the other formats presented at: http://www.xldynamic.com/source/xld.SUMPRODUCT.html I really need help here! As I said I have never gotten SumProduct to work !! thanks BAC |
I have never been able to get a SumProduct formula to work..He
Thanks, but that didn't work either. I get 0 for the results when I KNW it
should be over 12.5MM BAC "T. Valko" wrote: You're missing the argument delimiters and you had an extra set of ( ): SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3),--('Funding_Sub-Ledger'!$O$5:$O$346=C3),--(Aging!$A$3-'Funding_Sub-Ledger'!$I$5:$I$346<=30),--('Funding_Sub-Ledger'!$S$5:$S$3460),'Funding_Sub-Ledger'!$S$5:$S$346) -- Biff Microsoft Excel MVP "BAC" wrote in message ... I have never been able to get a SumProduct formula to work.. Now I Must Have it but the following is not filtering any of the limits and gives me the sum of the entire 'S' column. B3 and C3 are 3 character text fields. Aging!A3 is a date formatted field as is column I on the Funding_Sub-Ledger sheet. SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3)--('Funding_Sub-Ledger'!$O$5:$O$346=C3)--((Aging!$A$3 -'Funding_Sub-Ledger'!$I$5:$I$346)<=30)--('Funding_Sub-Ledger'!$S$5:$S$3460),('Funding_Sub-Ledger'!$S$5:$S$346)) I have tried using '*' in place of '--' as well as all the other formats presented at: http://www.xldynamic.com/source/xld.SUMPRODUCT.html I really need help here! As I said I have never gotten SumProduct to work !! thanks BAC |
I have never been able to get a SumProduct formula to work..He
Ok, then tell us what's in every one of your referenced ranges.
What's in: 'Funding_Sub-Ledger'!$N$5:$N$346 B3 'Funding_Sub-Ledger'!$O$5:$O$346 C3 Aging!$A$3 'Funding_Sub-Ledger'!$I$5:$I$346 'Funding_Sub-Ledger'!$S$5:$S$346 Are there negative numbers in 'Funding_Sub-Ledger'!$S$5:$S$346 ? -- Biff Microsoft Excel MVP "BAC" wrote in message ... Thanks, but that didn't work either. I get 0 for the results when I KNW it should be over 12.5MM BAC "T. Valko" wrote: You're missing the argument delimiters and you had an extra set of ( ): SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3),--('Funding_Sub-Ledger'!$O$5:$O$346=C3),--(Aging!$A$3-'Funding_Sub-Ledger'!$I$5:$I$346<=30),--('Funding_Sub-Ledger'!$S$5:$S$3460),'Funding_Sub-Ledger'!$S$5:$S$346) -- Biff Microsoft Excel MVP "BAC" wrote in message ... I have never been able to get a SumProduct formula to work.. Now I Must Have it but the following is not filtering any of the limits and gives me the sum of the entire 'S' column. B3 and C3 are 3 character text fields. Aging!A3 is a date formatted field as is column I on the Funding_Sub-Ledger sheet. SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3)--('Funding_Sub-Ledger'!$O$5:$O$346=C3)--((Aging!$A$3 -'Funding_Sub-Ledger'!$I$5:$I$346)<=30)--('Funding_Sub-Ledger'!$S$5:$S$3460),('Funding_Sub-Ledger'!$S$5:$S$346)) I have tried using '*' in place of '--' as well as all the other formats presented at: http://www.xldynamic.com/source/xld.SUMPRODUCT.html I really need help here! As I said I have never gotten SumProduct to work !! thanks BAC |
I have never been able to get a SumProduct formula to work..He
Thanks for you follow up, but my boss came over and re-formatted all the columns (which i thought I had already done), and your earlier response formula just started popping up answers! thanks again BAC "T. Valko" wrote: Ok, then tell us what's in every one of your referenced ranges. What's in: 'Funding_Sub-Ledger'!$N$5:$N$346 B3 'Funding_Sub-Ledger'!$O$5:$O$346 C3 Aging!$A$3 'Funding_Sub-Ledger'!$I$5:$I$346 'Funding_Sub-Ledger'!$S$5:$S$346 Are there negative numbers in 'Funding_Sub-Ledger'!$S$5:$S$346 ? -- Biff Microsoft Excel MVP "BAC" wrote in message ... Thanks, but that didn't work either. I get 0 for the results when I KNW it should be over 12.5MM BAC "T. Valko" wrote: You're missing the argument delimiters and you had an extra set of ( ): SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3),--('Funding_Sub-Ledger'!$O$5:$O$346=C3),--(Aging!$A$3-'Funding_Sub-Ledger'!$I$5:$I$346<=30),--('Funding_Sub-Ledger'!$S$5:$S$3460),'Funding_Sub-Ledger'!$S$5:$S$346) -- Biff Microsoft Excel MVP "BAC" wrote in message ... I have never been able to get a SumProduct formula to work.. Now I Must Have it but the following is not filtering any of the limits and gives me the sum of the entire 'S' column. B3 and C3 are 3 character text fields. Aging!A3 is a date formatted field as is column I on the Funding_Sub-Ledger sheet. SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3)--('Funding_Sub-Ledger'!$O$5:$O$346=C3)--((Aging!$A$3 -'Funding_Sub-Ledger'!$I$5:$I$346)<=30)--('Funding_Sub-Ledger'!$S$5:$S$3460),('Funding_Sub-Ledger'!$S$5:$S$346)) I have tried using '*' in place of '--' as well as all the other formats presented at: http://www.xldynamic.com/source/xld.SUMPRODUCT.html I really need help here! As I said I have never gotten SumProduct to work !! thanks BAC |
I have never been able to get a SumProduct formula to work..He
Ok, good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "BAC" wrote in message ... Thanks for you follow up, but my boss came over and re-formatted all the columns (which i thought I had already done), and your earlier response formula just started popping up answers! thanks again BAC "T. Valko" wrote: Ok, then tell us what's in every one of your referenced ranges. What's in: 'Funding_Sub-Ledger'!$N$5:$N$346 B3 'Funding_Sub-Ledger'!$O$5:$O$346 C3 Aging!$A$3 'Funding_Sub-Ledger'!$I$5:$I$346 'Funding_Sub-Ledger'!$S$5:$S$346 Are there negative numbers in 'Funding_Sub-Ledger'!$S$5:$S$346 ? -- Biff Microsoft Excel MVP "BAC" wrote in message ... Thanks, but that didn't work either. I get 0 for the results when I KNW it should be over 12.5MM BAC "T. Valko" wrote: You're missing the argument delimiters and you had an extra set of ( ): SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3),--('Funding_Sub-Ledger'!$O$5:$O$346=C3),--(Aging!$A$3-'Funding_Sub-Ledger'!$I$5:$I$346<=30),--('Funding_Sub-Ledger'!$S$5:$S$3460),'Funding_Sub-Ledger'!$S$5:$S$346) -- Biff Microsoft Excel MVP "BAC" wrote in message ... I have never been able to get a SumProduct formula to work.. Now I Must Have it but the following is not filtering any of the limits and gives me the sum of the entire 'S' column. B3 and C3 are 3 character text fields. Aging!A3 is a date formatted field as is column I on the Funding_Sub-Ledger sheet. SUMPRODUCT(--('Funding_Sub-Ledger'!$N$5:$N$346=B3)--('Funding_Sub-Ledger'!$O$5:$O$346=C3)--((Aging!$A$3 -'Funding_Sub-Ledger'!$I$5:$I$346)<=30)--('Funding_Sub-Ledger'!$S$5:$S$3460),('Funding_Sub-Ledger'!$S$5:$S$346)) I have tried using '*' in place of '--' as well as all the other formats presented at: http://www.xldynamic.com/source/xld.SUMPRODUCT.html I really need help here! As I said I have never gotten SumProduct to work !! thanks BAC |
| All times are GMT +1. The time now is 05:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com