Results based on multiple criteria

[Craig]

I have a simple 3 column spreadsheet for recording the date a particular
hedge is cut. Column A shows the cutting regime which is either 'Summer &
Winter' or 'Winter Only'. Column B shows the length of the hedge and Column C
shows the date it was cut.
What i would like to do is count the meters cut as the date is entered into
Column C so it gives a 'live' figure. By doing this we can advise customers
when we'll be working in their area because we'll know if we're behind or
ahead of schedule.

Thanks

 

[Daniel.C]

Can you post a sample data with the result you are expecting ?
Regards.
Daniel

 

[Craig]

Hi Daniel,

This is an example from our spreadsheet

Regime Length (m) Date
SUMMER & WINTER 45 16-Aug-08
SUMMER & WINTER 22 16-Aug-08
WINTER ONLY 28 15-Sep-08
SUMMER & WINTER 12 24-Sep-08

Meters cut to date _____

What i want to do is calculate the total meters cut each time a date is
entered into Column C. For instance on 16-Aug-08 the 'Meters cut to date' box
would show 67. On 15-Sep-08 the total would be 95. The idea is to create a
running total as each hedge is cut.
We used to measure the number of locations cut but that didn't give an
accurate picture as some hedges were 2 metres long whereas some could be 50
metres long. By measuring the distance cut it would give us a better picture
of how we are progressing.
Ideally we would like to count winter hedges only as not all hedges will be
cut twice a year but this may be more difficult. At the moment we would be
happy with just the distance cut.

Thanks in advance.

 

[Daniel.C]

Hi Craig,
The result should not be in column B :
=SUM(B:B)
If you want the result for a date (I assume, it is in D1 :
=SUMIF(C1:C10,"<="&D1,B1:B10)
If you want the same result for "winter only" :
=SUMIF(A1:A10,"winter only",B1:B10)
and for "winter only" and a date in D1 :
=SUMPRODUCT((A1:A10="winter only")*(C1:C10<=D1),B1:B10)
Regards.

 

[Craig]

Hi Daniel,

That worked. Thanks for your help

Craig