Help with SUMPRODUCT formula

[Craig]

I need some help with this formula. I think i've got it wrong.

Cutting Regime Length Date of cut Metres Cut
SUMMER & WINTER 36 17-Oct-08
SUMMER & WINTER 32 9-Sep-08
WINTER ONLY 22
SUMMER & WINTER 55 9-Sep-08

What i'm trying to do is calculate the total length of hedges cut as each
new date is entered.
Column A has the cutting regime, Column B is the hedge length, Column C is
the date the hedge was cut and Column D is where i'd like to see the result.
As each new date is entered the figure in Column D will grow until it
reaches our target (all hedges cut). For example On 9th Sept the figure would
have read 87 (32+55). Another hedge was cut on 17th Oct which measured 36
metres so the new total would read 123 (87+36). We only want the length of a
hedge added to the total if its been cut. To make things more complicated i
also need to split it between the two regimes. So i'd have a figure for
'summer & winter' and 'winter only'
This is the formula i had:-

=SUMPRODUCT((A2:A5='winter only')*(C2:C5<=D2),B2:B5)

The headings for each column are A1, B1 etc

This would really improve our performance monitoring so many thanks in
advance.

 

[Vítor Ferreira]

Hi Craig,
This is how SUMPRODUCT works: imagine that you have the following values:

A B
1 6
2 7
3 8
4 9
5 10

If you have a formula with SUMPRODUCT(A:A;B:B), it will sum the product from
the two arrays, that is saing: (A1*B1)+(A2*B2)+(A3*B3)+...

Hope, this helps!

 

[Vítor Ferreira]

By the way, in your case if you want to put a condition on the C column then
you should do the folowing: =SUMPRODUCT((A2:A5='winter
only')*(C2:C5<=D2)*(C2:C5),B2:B5).

Sorry for the two different posts!

 

[David Biddulph]

.... and you may find you need double quotes, rather than single, as the
delimiter of the text string.

=SUMPRODUCT((A2:A5="winter only")*(C2:C5<=D2)*(C2:C5),B2:B5)

 

[Craig]

That worked. Thanks for your help

By the way, in your case if you want to put a condition on the C column then
you should do the folowing: =SUMPRODUCT((A2:A5='winter
only')*(C2:C5<=D2)*(C2:C5),B2:B5).

Sorry for the two different posts!