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.

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!

"Craig" wrote:

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.

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!

"VÃ*tor Ferreira" wrote:

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!

"Craig" wrote:

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.

.... 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)
--
David Biddulph


"Vítor Ferreira" wrote in message
...

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!

"Vítor Ferreira" wrote:

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!

"Craig" wrote:

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.

That worked. Thanks for your help

"VÃ*tor Ferreira" wrote:


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!

"VÃ*tor Ferreira" wrote:

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!

"Craig" wrote:

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.