Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
I've got a table of data...
Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. The table has several transactions for one loan number. Can someone help?? My problem is looking up the loan number before summing the information I need. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
Do it like this:
=SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+ (B1:B100=6))*(C1:C100=11900)*(D1:D100)) where loan_num is the loan number you are interested in. Essentially, sum amount in column D where Loan Number = loan_num AND Account = 11900 AND Tran code is either 88 or 6. Adjust the ranges to suit the extent of your data. Hope this helps. Pete On Apr 24, 5:23*pm, LWilson wrote: I've got a table of data... Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. *The table has several transactions for one loan number. Can someone help?? *My problem is looking up the loan number before summing the information I need. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
My table of data is in one worksheet and the sumproduct formula is in
another. I need to be able to look up the loan number as well. When I used this formula, it gave me a #Name. Here's my formula... =SUMPRODUCT(download!A:A='Apr-08'!A7)*((download!H:H=88)+(download!H:H=6))*(down load!D:D=11900)*(download!F:F) "Pete_UK" wrote: Do it like this: =SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+ (B1:B100=6))*(C1:C100=11900)*(D1:D100)) where loan_num is the loan number you are interested in. Essentially, sum amount in column D where Loan Number = loan_num AND Account = 11900 AND Tran code is either 88 or 6. Adjust the ranges to suit the extent of your data. Hope this helps. Pete On Apr 24, 5:23 pm, LWilson wrote: I've got a table of data... Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. The table has several transactions for one loan number. Can someone help?? My problem is looking up the loan number before summing the information I need. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
If you are using XL2003 or earlier you can't use full-column
references in Sumproduct (or other array formulae), so you will have to make your ranges something like A1:A1000 instead. You have also missed a bracket off the end and at the very beginning. Hope this helps. Pete On Apr 24, 5:51*pm, LWilson wrote: My table of data is in one worksheet and the sumproduct formula is in another. *I need to be able to look up the loan number as well. *When I used this formula, it gave me a #Name. Here's my formula... =SUMPRODUCT(download!A:A='Apr-08'!A7)*((download!H:H=88)+(download!H:H=6))**(dow nload!D:D=11900)*(download!F:F) "Pete_UK" wrote: Do it like this: =SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+ (B1:B100=6))*(C1:C100=11900)*(D1:D100)) where loan_num is the loan number you are interested in. Essentially, sum amount in column D where Loan Number = loan_num AND Account = 11900 AND Tran code is either 88 or 6. Adjust the ranges to suit the extent of your data. Hope this helps. Pete On Apr 24, 5:23 pm, LWilson wrote: I've got a table of data... Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. *The table has several transactions for one loan number. Can someone help?? *My problem is looking up the loan number before summing the information I need.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
Now I feel really dumb. I just discovered I have excel 2000. I used the
range instead of the column and got a 0 for the answer. In one example, my answer should be 5039.89. I think the problem lies in the fact that I have two worksheets and it's having to pull the data from one and populate in the other?? Just not sure. I don't have much experience, obviously, with sumproduct. Would a nested sumif work or would that be too many parameters. I have 3 conditions to meet. loan number, trans code, account. If acct # is 4000000, and the trans code is an 88 and/or 6 and it's in account 11900, then give me the sum of (amounts for trans code 88 and 6). You're very kind to help. "Pete_UK" wrote: If you are using XL2003 or earlier you can't use full-column references in Sumproduct (or other array formulae), so you will have to make your ranges something like A1:A1000 instead. You have also missed a bracket off the end and at the very beginning. Hope this helps. Pete On Apr 24, 5:51 pm, LWilson wrote: My table of data is in one worksheet and the sumproduct formula is in another. I need to be able to look up the loan number as well. When I used this formula, it gave me a #Name. Here's my formula... =SUMPRODUCT(download!A:A='Apr-08'!A7)*((download!H:H=88)+(download!H:H=6))*Â*(do wnload!D:D=11900)*(download!F:F) "Pete_UK" wrote: Do it like this: =SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+ (B1:B100=6))*(C1:C100=11900)*(D1:D100)) where loan_num is the loan number you are interested in. Essentially, sum amount in column D where Loan Number = loan_num AND Account = 11900 AND Tran code is either 88 or 6. Adjust the ranges to suit the extent of your data. Hope this helps. Pete On Apr 24, 5:23 pm, LWilson wrote: I've got a table of data... Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. The table has several transactions for one loan number. Can someone help?? My problem is looking up the loan number before summing the information I need.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
You won't be able to use SUMIF because you have more than one
condition. Just Copy/Paste your formula to the newsgroups so we can see where it might be going wrong. Pete On Apr 24, 10:01*pm, LWilson wrote: Now I feel really dumb. *I just discovered I have excel 2000. *I used the range instead of the column and got a 0 for the answer. In one example, my answer should be 5039.89. *I think the problem lies in the fact that I have two worksheets and it's having to pull the data from one and populate in the other?? Just not sure. *I don't have much experience, obviously, with sumproduct. *Would a nested sumif work or would that be too many parameters. I have 3 conditions to meet. loan number, trans code, account. * If acct # is 4000000, and the trans code is an 88 and/or 6 and it's in account 11900, then give me the sum of (amounts for trans code 88 and 6). * You're very kind to help. "Pete_UK" wrote: If you are using XL2003 or earlier you can't use full-column references in Sumproduct (or other array formulae), so you will have to make your ranges something like A1:A1000 instead. You have also missed a bracket off the end and at the very beginning. Hope this helps. Pete On Apr 24, 5:51 pm, LWilson wrote: My table of data is in one worksheet and the sumproduct formula is in another. *I need to be able to look up the loan number as well. *When I used this formula, it gave me a #Name. Here's my formula... =SUMPRODUCT(download!A:A='Apr-08'!A7)*((download!H:H=88)+(download!H:H=6))***(do wnload!D:D=11900)*(download!F:F) "Pete_UK" wrote: Do it like this: =SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+ (B1:B100=6))*(C1:C100=11900)*(D1:D100)) where loan_num is the loan number you are interested in. Essentially, sum amount in column D where Loan Number = loan_num AND Account = 11900 AND Tran code is either 88 or 6. Adjust the ranges to suit the extent of your data. Hope this helps. Pete On Apr 24, 5:23 pm, LWilson wrote: I've got a table of data... Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. *The table has several transactions for one loan number. Can someone help?? *My problem is looking up the loan number before summing the information I need.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
Here is the formula I have
=SUMPRODUCT(((download!A2:A2990=A7)*((download!H2: H2990=88)+(download!H2:H2990=6))*(download!D2960:D 2990=11900)*(download!F2:F2990))) "Pete_UK" wrote: You won't be able to use SUMIF because you have more than one condition. Just Copy/Paste your formula to the newsgroups so we can see where it might be going wrong. Pete On Apr 24, 10:01 pm, LWilson wrote: Now I feel really dumb. I just discovered I have excel 2000. I used the range instead of the column and got a 0 for the answer. In one example, my answer should be 5039.89. I think the problem lies in the fact that I have two worksheets and it's having to pull the data from one and populate in the other?? Just not sure. I don't have much experience, obviously, with sumproduct. Would a nested sumif work or would that be too many parameters. I have 3 conditions to meet. loan number, trans code, account. If acct # is 4000000, and the trans code is an 88 and/or 6 and it's in account 11900, then give me the sum of (amounts for trans code 88 and 6). You're very kind to help. "Pete_UK" wrote: If you are using XL2003 or earlier you can't use full-column references in Sumproduct (or other array formulae), so you will have to make your ranges something like A1:A1000 instead. You have also missed a bracket off the end and at the very beginning. Hope this helps. Pete On Apr 24, 5:51 pm, LWilson wrote: My table of data is in one worksheet and the sumproduct formula is in another. I need to be able to look up the loan number as well. When I used this formula, it gave me a #Name. Here's my formula... =SUMPRODUCT(download!A:A='Apr-08'!A7)*((download!H:H=88)+(download!H:H=6))*Â*Â*( download!D:D=11900)*(download!F:F) "Pete_UK" wrote: Do it like this: =SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+ (B1:B100=6))*(C1:C100=11900)*(D1:D100)) where loan_num is the loan number you are interested in. Essentially, sum amount in column D where Loan Number = loan_num AND Account = 11900 AND Tran code is either 88 or 6. Adjust the ranges to suit the extent of your data. Hope this helps. Pete On Apr 24, 5:23 pm, LWilson wrote: I've got a table of data... Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. The table has several transactions for one loan number. Can someone help?? My problem is looking up the loan number before summing the information I need.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
You have:
(download!D2960:D2990=11900) as the fourth term, but this should be: (download!D2:D2990=11900) The ranges should be the same length. Also, as this in an account number it may be entered as text - you might have to put it as: (download!D2:D2990="11900") (but try the earlier change first). Hope this helps. Pete On Apr 25, 1:49*pm, LWilson wrote: Here is the formula I have =SUMPRODUCT(((download!A2:A2990=A7)*((download!H2: H2990=88)+(download!H2:H2*990=6))*(download!D2960: D2990=11900)*(download!F2:F2990))) "Pete_UK" wrote: You won't be able to use SUMIF because you have more than one condition. Just Copy/Paste your formula to the newsgroups so we can see where it might be going wrong. Pete On Apr 24, 10:01 pm, LWilson wrote: Now I feel really dumb. *I just discovered I have excel 2000. *I used the range instead of the column and got a 0 for the answer. In one example, my answer should be 5039.89. *I think the problem lies in the fact that I have two worksheets and it's having to pull the data from one and populate in the other?? Just not sure. *I don't have much experience, obviously, with sumproduct. *Would a nested sumif work or would that be too many parameters. I have 3 conditions to meet. loan number, trans code, account. * If acct # is 4000000, and the trans code is an 88 and/or 6 and it's in account 11900, then give me the sum of (amounts for trans code 88 and 6). * You're very kind to help. "Pete_UK" wrote: If you are using XL2003 or earlier you can't use full-column references in Sumproduct (or other array formulae), so you will have to make your ranges something like A1:A1000 instead. You have also missed a bracket off the end and at the very beginning.. Hope this helps. Pete On Apr 24, 5:51 pm, LWilson wrote: My table of data is in one worksheet and the sumproduct formula is in another. *I need to be able to look up the loan number as well. *When I used this formula, it gave me a #Name. Here's my formula... =SUMPRODUCT(download!A:A='Apr-08'!A7)*((download!H:H=88)+(download!H:H=6))****(d ownload!D:D=11900)*(download!F:F) "Pete_UK" wrote: Do it like this: =SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+ (B1:B100=6))*(C1:C100=11900)*(D1:D100)) where loan_num is the loan number you are interested in. Essentially, sum amount in column D where Loan Number = loan_num AND Account = 11900 AND Tran code is either 88 or 6. Adjust the ranges to suit the extent of your data. Hope this helps. Pete On Apr 24, 5:23 pm, LWilson wrote: I've got a table of data... Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. *The table has several transactions for one loan number. Can someone help?? *My problem is looking up the loan number before summing the information I need.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
Still getting a 0 sum. Now I don't need to multiply...or is this specific to
the sumproduct function? =SUMPRODUCT(((download!$A$2:$A$2990=A7)*((download !$H$2:$H$2990=88)+(download!$H$2:$H$2990=6))*(down load!$D$2:$D$2990="11900")*(download!$F$2:$F$2990) )) "Pete_UK" wrote: You have: (download!D2960:D2990=11900) as the fourth term, but this should be: (download!D2:D2990=11900) The ranges should be the same length. Also, as this in an account number it may be entered as text - you might have to put it as: (download!D2:D2990="11900") (but try the earlier change first). Hope this helps. Pete On Apr 25, 1:49 pm, LWilson wrote: Here is the formula I have =SUMPRODUCT(((download!A2:A2990=A7)*((download!H2: H2990=88)+(download!H2:H2Â*990=6))*(download!D2960 :D2990=11900)*(download!F2:F2990))) "Pete_UK" wrote: You won't be able to use SUMIF because you have more than one condition. Just Copy/Paste your formula to the newsgroups so we can see where it might be going wrong. Pete On Apr 24, 10:01 pm, LWilson wrote: Now I feel really dumb. I just discovered I have excel 2000. I used the range instead of the column and got a 0 for the answer. In one example, my answer should be 5039.89. I think the problem lies in the fact that I have two worksheets and it's having to pull the data from one and populate in the other?? Just not sure. I don't have much experience, obviously, with sumproduct. Would a nested sumif work or would that be too many parameters. I have 3 conditions to meet. loan number, trans code, account. If acct # is 4000000, and the trans code is an 88 and/or 6 and it's in account 11900, then give me the sum of (amounts for trans code 88 and 6). You're very kind to help. "Pete_UK" wrote: If you are using XL2003 or earlier you can't use full-column references in Sumproduct (or other array formulae), so you will have to make your ranges something like A1:A1000 instead. You have also missed a bracket off the end and at the very beginning.. Hope this helps. Pete On Apr 24, 5:51 pm, LWilson wrote: My table of data is in one worksheet and the sumproduct formula is in another. I need to be able to look up the loan number as well. When I used this formula, it gave me a #Name. Here's my formula... =SUMPRODUCT(download!A:A='Apr-08'!A7)*((download!H:H=88)+(download!H:H=6))*Â*Â*Â *(download!D:D=11900)*(download!F:F) "Pete_UK" wrote: Do it like this: =SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+ (B1:B100=6))*(C1:C100=11900)*(D1:D100)) where loan_num is the loan number you are interested in. Essentially, sum amount in column D where Loan Number = loan_num AND Account = 11900 AND Tran code is either 88 or 6. Adjust the ranges to suit the extent of your data. Hope this helps. Pete On Apr 24, 5:23 pm, LWilson wrote: I've got a table of data... Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. The table has several transactions for one loan number. Can someone help?? My problem is looking up the loan number before summing the information I need.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
The * symbol is the same as AND and the + symbol is equivalent to OR
in this formula. Did you try the formula without the quotes around 11900 ? It just occurred to me that the 88 and 6 in column H might also be text values, so you may need to put quotes around both of those. I presume that A7 does contain something that matches exactly with some cells in column A? No extra spaces or things like that which would cause a mis-match? Perhaps you should just copy/paste the value from one of the cells in the download sheet into A7 of this sheet. Hope this helps (we'll get there in the end!!). Pete On Apr 25, 2:25*pm, LWilson wrote: Still getting a 0 sum. *Now I don't need to multiply...or is this specific to the sumproduct function? * =SUMPRODUCT(((download!$A$2:$A$2990=A7)*((download !$H$2:$H$2990=88)+(downlo*ad!$H$2:$H$2990=6))*(dow nload!$D$2:$D$2990="11900")*(download!$F$2:$F$2990 )*)) "Pete_UK" wrote: You have: (download!D2960:D2990=11900) as the fourth term, but this should be: (download!D2:D2990=11900) The ranges should be the same length. Also, as this in an account number it may be entered as text - you might have to put it as: (download!D2:D2990="11900") (but try the earlier change first). Hope this helps. Pete On Apr 25, 1:49 pm, LWilson wrote: Here is the formula I have =SUMPRODUCT(((download!A2:A2990=A7)*((download!H2: H2990=88)+(download!H2:H2**990=6))*(download!D2960 :D2990=11900)*(download!F2:F2990))) "Pete_UK" wrote: You won't be able to use SUMIF because you have more than one condition. Just Copy/Paste your formula to the newsgroups so we can see where it might be going wrong. Pete On Apr 24, 10:01 pm, LWilson wrote: Now I feel really dumb. *I just discovered I have excel 2000. *I used the range instead of the column and got a 0 for the answer. In one example, my answer should be 5039.89. *I think the problem lies in the fact that I have two worksheets and it's having to pull the data from one and populate in the other?? Just not sure. *I don't have much experience, obviously, with sumproduct. *Would a nested sumif work or would that be too many parameters. I have 3 conditions to meet. loan number, trans code, account. * If acct # is 4000000, and the trans code is an 88 and/or 6 and it's in account 11900, then give me the sum of (amounts for trans code 88 and 6). * You're very kind to help. "Pete_UK" wrote: If you are using XL2003 or earlier you can't use full-column references in Sumproduct (or other array formulae), so you will have to make your ranges something like A1:A1000 instead. You have also missed a bracket off the end and at the very beginning.. Hope this helps. Pete On Apr 24, 5:51 pm, LWilson wrote: My table of data is in one worksheet and the sumproduct formula is in another. *I need to be able to look up the loan number as well. *When I used this formula, it gave me a #Name. Here's my formula... =SUMPRODUCT(download!A:A='Apr-08'!A7)*((download!H:H=88)+(download!H:H=6))*****( download!D:D=11900)*(download!F:F) "Pete_UK" wrote: Do it like this: =SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+ (B1:B100=6))*(C1:C100=11900)*(D1:D100)) where loan_num is the loan number you are interested in. Essentially, sum amount in column D where Loan Number = loan_num AND Account = 11900 AND Tran code is either 88 or 6. Adjust the ranges to suit the extent of your data. Hope this helps. Pete On Apr 24, 5:23 pm, LWilson wrote: I've got a table of data... Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. *The table has several transactions for one loan number. Can someone help?? *My problem is looking up the loan number before summing the information I need.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
Pete,
Yes, I tried quotes around the 11900 and I have the same result. I've also gone ahead and added quotes to the other items for 88 and 6 with the same result...0. As far as the A7, this column has the loan number in it . The loan number is in the download worksheet of the workbook in column A. It's as if it's not recognizing the loan number. Again, thanks for all your help. "Pete_UK" wrote: The * symbol is the same as AND and the + symbol is equivalent to OR in this formula. Did you try the formula without the quotes around 11900 ? It just occurred to me that the 88 and 6 in column H might also be text values, so you may need to put quotes around both of those. I presume that A7 does contain something that matches exactly with some cells in column A? No extra spaces or things like that which would cause a mis-match? Perhaps you should just copy/paste the value from one of the cells in the download sheet into A7 of this sheet. Hope this helps (we'll get there in the end!!). Pete On Apr 25, 2:25 pm, LWilson wrote: Still getting a 0 sum. Now I don't need to multiply...or is this specific to the sumproduct function? =SUMPRODUCT(((download!$A$2:$A$2990=A7)*((download !$H$2:$H$2990=88)+(downloÂ*ad!$H$2:$H$2990=6))*(do wnload!$D$2:$D$2990="11900")*(download!$F$2:$F$299 0)Â*)) "Pete_UK" wrote: You have: (download!D2960:D2990=11900) as the fourth term, but this should be: (download!D2:D2990=11900) The ranges should be the same length. Also, as this in an account number it may be entered as text - you might have to put it as: (download!D2:D2990="11900") (but try the earlier change first). Hope this helps. Pete On Apr 25, 1:49 pm, LWilson wrote: Here is the formula I have =SUMPRODUCT(((download!A2:A2990=A7)*((download!H2: H2990=88)+(download!H2:H2Â*Â*990=6))*(download!D29 60:D2990=11900)*(download!F2:F2990))) "Pete_UK" wrote: You won't be able to use SUMIF because you have more than one condition. Just Copy/Paste your formula to the newsgroups so we can see where it might be going wrong. Pete On Apr 24, 10:01 pm, LWilson wrote: Now I feel really dumb. I just discovered I have excel 2000. I used the range instead of the column and got a 0 for the answer. In one example, my answer should be 5039.89. I think the problem lies in the fact that I have two worksheets and it's having to pull the data from one and populate in the other?? Just not sure. I don't have much experience, obviously, with sumproduct. Would a nested sumif work or would that be too many parameters. I have 3 conditions to meet. loan number, trans code, account. If acct # is 4000000, and the trans code is an 88 and/or 6 and it's in account 11900, then give me the sum of (amounts for trans code 88 and 6). You're very kind to help. "Pete_UK" wrote: If you are using XL2003 or earlier you can't use full-column references in Sumproduct (or other array formulae), so you will have to make your ranges something like A1:A1000 instead. You have also missed a bracket off the end and at the very beginning.. Hope this helps. Pete On Apr 24, 5:51 pm, LWilson wrote: My table of data is in one worksheet and the sumproduct formula is in another. I need to be able to look up the loan number as well. When I used this formula, it gave me a #Name. Here's my formula... =SUMPRODUCT(download!A:A='Apr-08'!A7)*((download!H:H=88)+(download!H:H=6))*Â*Â*Â *Â*(download!D:D=11900)*(download!F:F) "Pete_UK" wrote: Do it like this: =SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+ (B1:B100=6))*(C1:C100=11900)*(D1:D100)) where loan_num is the loan number you are interested in. Essentially, sum amount in column D where Loan Number = loan_num AND Account = 11900 AND Tran code is either 88 or 6. Adjust the ranges to suit the extent of your data. Hope this helps. Pete On Apr 24, 5:23 pm, LWilson wrote: I've got a table of data... Loan number, tran code, acct, amt. I need to sum the amounts for the loan number that equal a certain tran code and acct number. For example, sum acct #11900 for tran codes 88 and 6 for a certain loan number. The table has several transactions for one loan number. Can someone help?? My problem is looking up the loan number before summing the information I need.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
I suggest you build up the formula in stages to try to pinpoint where
it is not working, For example, try this: =SUMPRODUCT((download!$A$2:$A$2990=A7)*(download!$ F$2:$F$2990*)*) This should give you the sum of column F for all loan numbers that match A7. Then you could try the following: =SUMPRODUCT((download!$D$2:$D$2990="11900")*(downl oad!$F$2:$F$2990*)*) with and without the quotes around 11900, and this should give you the sum where column D equals 11900. Do this for the other terms in turn, and then start to build them up to the composite formula, checking things out at each stage. It might be better with just a small set of data so that you can more easily check out the totals yourself. Hope this helps. Pete On Apr 25, 4:44*pm, LWilson wrote: Pete, Yes, I tried quotes around the 11900 and I have the same result. I've also gone ahead and added quotes to the other items for 88 and 6 with the same result...0. * As far as the A7, this column has the loan number in it . The loan number is in the download worksheet of the workbook in column A. *It's as if it's not recognizing the loan number. * Again, thanks for all your help. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting
Thank you for all your help. I was able to get the formula to work. I got
it done several days ago and just now have had a chance to let you know. Thanks again! "Pete_UK" wrote: I suggest you build up the formula in stages to try to pinpoint where it is not working, For example, try this: =SUMPRODUCT((download!$A$2:$A$2990=A7)*(download!$ F$2:$F$2990Â*)Â*) This should give you the sum of column F for all loan numbers that match A7. Then you could try the following: =SUMPRODUCT((download!$D$2:$D$2990="11900")*(downl oad!$F$2:$F$2990Â*)Â*) with and without the quotes around 11900, and this should give you the sum where column D equals 11900. Do this for the other terms in turn, and then start to build them up to the composite formula, checking things out at each stage. It might be better with just a small set of data so that you can more easily check out the totals yourself. Hope this helps. Pete On Apr 25, 4:44 pm, LWilson wrote: Pete, Yes, I tried quotes around the 11900 and I have the same result. I've also gone ahead and added quotes to the other items for 88 and 6 with the same result...0. As far as the A7, this column has the loan number in it . The loan number is in the download worksheet of the workbook in column A. It's as if it's not recognizing the loan number. Again, thanks for all your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting | Excel Worksheet Functions | |||
OR Nesting? | Excel Discussion (Misc queries) | |||
NESTING FORMULA | Excel Worksheet Functions | |||
IF - Nesting... almost got it - need a bit of help | Excel Worksheet Functions | |||
nesting sum if and | Excel Worksheet Functions |