calculating from 3 different columns in a worksheet

  • Thread starter Thread starter dawgy
  • Start date Start date
D

dawgy

I am working on a spreadsheet that has 3 columns (a,b,c) A has the mont
of the year (jan, feb, mar, etc....) B has the type of loan that I a
working on (denied, withdrawn, completed, etc...). C has the value o
each loan.

I need a formula to calculate the total amount of completed Januar
loans
 
The previous reply from Shades did not work. If anyone else has any
suggestions. I'm all ears
 
Yeah, it didn't work for a good reason. I didn't finish including the
second clause. Sorry about that.

Here is what it should be:

=SUMPRODUCT(($A1:$A$50="Jan")*($B$1:$B$50="completed"),$C$1:$C$50))
 
I can't get anything to work!!!!!!!!!! If anyone can figure this out ,
it would help me tremendously
 
Assuming your cols A, B, C data is in Sheet1,
say from row2 down to row1000

Try in another sheet, say, Sheet2:
------------------------------------
Put in A2: Jan
Put in B2: completed

Put in C2:

=SUMPRODUCT((Sheet1!$A$2:$A$1000="Jan")*(Sheet1!$B$2:$B$1000="completed")*Sh
eet1!$C$2:$C$1000)

C2 will return the total amount of "completed" Jan loans,
ie the total of amounts in col C
for which col A = "Jan" and col B = "completed"

And if you have yet other combinations of values
in cols A & B to similarly evaluate,
(e.g.: Jan - denied, Jan - withdrawn, Feb - completed, etc)
just put these in A3 & B3, A4 & B4, etc viz. down cols A & B.

Then just copy C2 down the col C to calculate all the corresponding results.

Amend the range references to suit,
but note that the 3 ranges must remain identical, viz.:

Sheet1!$A$2:$A$1000
Sheet1!$B$2:$B$1000
Sheet1!$C$2:$C$1000

and you cannot use complete cols, e.g.: A:A, B:B, C:C in sumproduct
 
Whoops! sorry..

Pl correct the formula in C2 to read:

=SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2:$B$1000=B2)*Sheet1!$C$2:$C
$1000)
 
I'm not sure what's wrong. I still can't get it to give me a total. What
are the $ signs for? I've tried it with and without them. Thanks for
your help.
 
take a look at
Fill Handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

the $A would mean that will the formula is copied with the
fill handle to the right it would not change it still refers to column A.
The $2 would mean that that part of the address will still refer to
row 2 regardless of what row it is copied to with the fill handle.

So that the range $A$2:$A$100 means A2:A100 but it
will not change when the formula is dragged downward.
The A2 will change to A3 on the next row and to A4 on the
row after that.
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Pl correct the formula in C2 to read:

=SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2:$B$1000=B2)*Sheet1!$C$2:$C
$1000)
 
I have received some great information since I started writing thi
inquiry.

Everybody keeps giving me a version of the same formula

=SUMPRODUCT(('1999'!$D$2:$D$600="January")*('1999'!$E$2:$E$600="closed")*'1999'!$H$2:$H$600
(I copy and pasted this directly from the spreadsheet I have bee
working on)

I have tried many variations of this formula, but I can still not ge
it to show the information correctly. What am I doing wrong
 
I just copied the formula so that I could refer to parts of it in
explaining the absolute ($) portions of it.

I'm afraid that your original question was not specific enough, so
those that replied with formulas had to make up a formula for a
hypothetical spreadsheet(s). The '1999'!$D$2:$D$600
would refer to a range on the spreadsheet 1999, for instance.
 
David,

Thank you for clearing up my lack of sophistication. Do you know what
the correct formula would be?
 
dawgy > said:
=SUMPRODUCT(('1999'!$D$2:$D$600="January")*('1999'!$E$2:$E$600="closed")*'19
99'!$H$2:$H$600)
(I copy and pasted this directly from the spreadsheet I have been
working on)
Click to expand...

Ok, so the above describes more of your specific situation.

I believe what you may actually have in col D are *dates*, e.g.:

1-Jan-1999
2-Jan-1999
3-Jan-1999
1-Feb-1999
etc

If so, then this specific modification of the SUMPRODUCT
should work for you:

Try in a *new* sheet say, Sheet2:
------------------------------------
Put in A2: 1-Jan-1999
Put in B2: completed

Put in C2:
=SUMPRODUCT((MONTH('1999'!$D$2:$D$600)=MONTH(A2))*(TRIM('1999'!$E$2:$E$600)=
TRIM(B2))*'1999'!$H$2:$H$600)

C2 will return the total of the amounts in col H in sheet: 1999
for which col D in sheet:1999 has January *dates*
and col B in sheet: 1999 contains the phrase "completed"

TRIM() is used to make it more robust in case there are "invisible"
extraneous spaces in the text-strings compared

If you put in A3: 1-Feb-1999, in B3: completed,
and so on down cols A and B
just copy C2 down to col C to return corresponding values
 
C2 will return the total of the amounts in col H in sheet: 1999
for which col D in sheet:1999 has January *dates*
and col B in sheet: 1999 contains the phrase "completed"
Click to expand...

Typo correction: "col B" above should read "col E'
 
Back
Top