calculating from 3 different columns in a worksheet

[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

 

[shades]

=SUMPRODUCT(($A1:$A$50="Jan")*($B$1:$B$50),$C$1:$C$50))

 

[dawgy]

The previous reply from Shades did not work. If anyone else has any
suggestions. I'm all ears

 

[shades]

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))

 

[dawgy]

I can't get anything to work!!!!!!!!!! If anyone can figure this out ,
it would help me tremendously

 

[Max]

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

 

[Max]

Think your:

=SUMPRODUCT(($A1:$A$50="Jan")*($B$1:$B$50="completed"),$C$1:$C$50))

should read as:

=SUMPRODUCT(($A1:$A$50="Jan")*($B$1:$B$50="completed")*$C$1:$C$50))

with "*" instead of "," just before $C$1:$C$50

 

[Max]

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)

 

[dawgy]

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.

 

[David McRitchie]

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)

 

[dawgy]

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

 

[David McRitchie]

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.

 

[dawgy]

David,

Thank you for clearing up my lack of sophistication. Do you know what
the correct formula would be?

 

[Max]

=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)

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

 

[Max]

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"

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