conditional summing formula problems

[christopherp]

Hi Guys,

Below are cells from my spreadsheet.

Richard 30-Jul-04 $92,000.00
Richard 30-Jul-04 $434,000.00
Richard 23-Jul-04 $10,000.00
Rod 23-Jun-04 $360,500.00
Rod 20-Jul-04 $75,000.00
Rod 3-Aug-04 $208,000.00
Dennis 29-Jul-04 $226,500.00
Michelle 26-Jul-04 $128,000.00
Richard 25-Jun-04 $160,000.00
Dennis 22-Jun-04 $135,000.00
Richard 15-Jul-04 $230,000.00
Dennis 04-Jun-04 $110,000.00
Rod 16-Jul-04 $48,000.00
Greg 27-Mar-04 $100,000.00

What I would like to do is work out the value of all loans written b
each loan writer in a given date range.

This formula will be run on a seperate worksheet to the one containin
the data

To make it easier as the months go by I plan to enter the start an
finish date for the date range in two cells i.e. (>a2<a3)

where a2 contains 01/07/04 and a3 contains 31/07/04

so far I have come up with:


=IF(Refinances!E4:E50="rod",IF(Refinances!F4:F50>Stats!
F2<Stats!F3,""))

I know this is not even close but hey i am trying.

I will being running this formula in a seperate cell for each loa
writer so in the formula above I will change "rod" to each loan writer
name

Where I am running into trouble is summing only the amounts in th
third column which correspond to the appropriate loan writer

I hope it is clear what I would like to do, I just know someone ou
there could do this with their eyes closed (are you out there Fran
from Germany?)

Thanks a bunch

Chri

 

[Peo Sjoblom]

One way

=SUMIF(Date_Range,">"&A2,Dollar_Range)-SUMIF(Date_Range,">="&A3,Dollar_Range
)

or

=SUMPRODUCT(--(Date_Range>A2),--(Date_Range<A3),Dollar_Range)


the first formula is probably a bit more efficient

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Jazzer]

Hi,

Put the following array formula anywhere in Stats -sheet :

=SUM((Refinances!A1:A14="Rod")*(Refinances!B1:B14>=Stats!A2)*(Refinances!B1:B14<=Stats!A3)*Refinances!C1:C14)

Because it's an array formula, you have to enter it by pressin
Ctrl+Shift+Enter

Change the referenced areas, so that they fit to your datatable an
remember that all the areas in the formula must be identical.

As your database gets bigger, you would like to make the reference
areas much bigger, but because this is array formula, you cannot use
reference that has no line number. i.e. you can use thi
"Refinances!A1:A65535", but not this "Refinances!A:A".

Hope this helps.

- Asse

 

[mzehr]

Hi,
you might try the following:
=SUMPRODUCT((Sheet1!A1:A14="Richard")*(Sheet1!
B1:B14>=Sheet2!A2)*(Sheet1!B1:B14<=Sheet2!A3)*(Sheet1!
C1:C14))

But a better move might be to use a pivot table to
summarize your data.

HTH

 

[christopherp]

Thanks agai