Formula assistance required
 

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

Formula assistance required
 

Try

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

--

HTH

Bob

"J.Scargill" wrote in message
...
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!!).

Formula assistance required
 

Hi,

Try this with the date ranges in a1 & a2

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

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

Formula assistance required
 

Bob, you are immense! Many thanks.

"Bob Phillips" wrote:

Try

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

--

HTH

Bob

"J.Scargill" wrote in message
...
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!!).



.