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.

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.

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.

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 -

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 -

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 -