Formula assistance required

J

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!!).
 
B

Bob Phillips

Try

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

Mike H

Hi,

Try this with the date ranges in a1 & a2

=SUMPRODUCT((A14:A1000>=A1)*(A14:A1000<=A2)*(P14:p1000="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

J.Scargill

Bob, you are immense! Many thanks.

Bob Phillips said:
Try

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

--

HTH

Bob




.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top