Formula assistance required

[J.Scargill]

Hi guys, am sure you'll be able to help with this but I just cant figure it
out;

Worksheet looks a little like this;

A P U
14 27/02/10 Y 10.00
15 04/03/10 Y 15.00
16 05/03/10 N 0.00
17 06/03/10 N 0.00
18 07/03/10 Y 12.00

My data is in rows 14 to 1000. Col A is the date the claim has been
submitted, Col P is Yes or No to whether claim has been accepted and Col U is
the Loss value.
I have a summary table set up and am wanting to have a weekly total of the
amount payable for accepted claims. So, for week 1, if cell in Col A is
between 27/02/10 and 06/03/10 AND there is a Y in Col P, sum the value in the
corresponding cell in column U. I can then just change the date range for the
remaining weeks of the year and it will automtically populate once the data
is added.

Hope this makes sense and appreciate your expert help (again!!).

 

[Bob Phillips]

Try

=SUMPRODUCT(--(A14:A1000>=--"2010-02-27"),--(A14:A1000<=--"2010-03-06"),--(P141000="Y"),U14:U1000)

 

[Mike H]

Hi,

Try this with the date ranges in a1 & a2

=SUMPRODUCT((A14:A1000>=A1)*(A14:A1000<=A2)*(P141000="Y")*(U14:U1000))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[J.Scargill]

Bob, you are immense! Many thanks.

Try

=SUMPRODUCT(--(A14:A1000>=--"2010-02-27"),--(A14:A1000<=--"2010-03-06"),--(P141000="Y"),U14:U1000)

--

HTH

Bob




.