I am looking at four columns:

A1 = Scorecard Date
B1:B20 = Different dates
D1:D20 = Record Value
E1:E20 = Spend

I would like to do the following:
For all records where the dates in column B match the date shown in A1, I
would like to sum D1:D20 and divide the total by the sum of E1:E20.

=SUMPRODUCT(--(B1:B20=A1),D1;D20/E1/E20)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"juliejg1" wrote in message
...
I am looking at four columns:

A1 = Scorecard Date
B1:B20 = Different dates
D1:D20 = Record Value
E1:E20 = Spend

I would like to do the following:
For all records where the dates in column B match the date shown in A1, I
would like to sum D1:D20 and divide the total by the sum of E1:E20.

Sorry, posted too quickly.

This is what I wanted to post

=SUM(IF(E1:E20<"",IF(B1:B20=A1,D1:D20/E1:E20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"juliejg1" wrote in message
...
I am looking at four columns:

A1 = Scorecard Date
B1:B20 = Different dates
D1:D20 = Record Value
E1:E20 = Spend

I would like to do the following:
For all records where the dates in column B match the date shown in A1, I
would like to sum D1:D20 and divide the total by the sum of E1:E20.

Thank you

"Bob Phillips" wrote:

Sorry, posted too quickly.

This is what I wanted to post

=SUM(IF(E1:E20<"",IF(B1:B20=A1,D1:D20/E1:E20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"juliejg1" wrote in message
...
I am looking at four columns:

A1 = Scorecard Date
B1:B20 = Different dates
D1:D20 = Record Value
E1:E20 = Spend

I would like to do the following:
For all records where the dates in column B match the date shown in A1, I
would like to sum D1:D20 and divide the total by the sum of E1:E20.