Nesting

[LWilson]

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.

 

[Pete_UK]

Do it like this:

=SUMPRODUCT((A1:A100=loan_num)*((B1:B100=88)+
(B1:B100=6))*(C1:C100=11900)*(D1100))

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

 

[LWilson]

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=11900)*(download!F:F)

 

[Pete_UK]

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

 

[LWilson]

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]

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

 

[LWilson]

Here is the formula I have

=SUMPRODUCT(((download!A2:A2990=A7)*((download!H2:H2990=88)+(download!H2:H2990=6))*(download!D29602990=11900)*(download!F2:F2990)))

 

[Pete_UK]

You have:

(download!D29602990=11900)

as the fourth term, but this should be:

(download!D22990=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!D22990="11900")

(but try the earlier change first).

Hope this helps.

Pete

 

[LWilson]

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))*(download!$D$2:$D$2990="11900")*(download!$F$2:$F$2990)))

 

[Pete_UK]

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

 

[LWilson]

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]

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")*(download!$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

 

[LWilson]

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!