SUMPRODUCT question

[juliejg1]

I am looking at four columns:

A1 = Scorecard Date
B1:B20 = Different dates
D120 = 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 D120 and divide the total by the sum of E1:E20.

 

[Bob Phillips]

=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)

 

[Bob Phillips]

Sorry, posted too quickly.

This is what I wanted to post

=SUM(IF(E1:E20<>"",IF(B1:B20=A1,D120/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]

Thank you

Sorry, posted too quickly.

This is what I wanted to post

=SUM(IF(E1:E20<>"",IF(B1:B20=A1,D120/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)