| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct does not work after editing data | Excel Worksheet Functions | |||
| SUMPRODUCT vs. COUNTIF -- Why does one work? | Excel Worksheet Functions | |||
| sumproduct doesn't work | Excel Worksheet Functions | |||
| Sumproduct doesn't work with columns... alternatives? | Excel Worksheet Functions | |||
| Will SUMPRODUCT work for this? | Excel Discussion (Misc queries) | |||
Hybrid Mode
