Count and sum values for previous week

**qflyer** · 02-25-2011, 12:53 AM

Spreadsheet is a record of missions performed over the past year. Column B is the date of missions, E is the length of the mission in hours (1.5, 2.7, 4.6 etc.)

I need to count the number of missions performed during the previous week (the previous Mon - Sun...not the previous 7 days) So today is Thursday Feb 24, I need to count the number of missions performed between Monday the 14th and Sunday the 20th.

I also need to sum the total mission hours (column E) during the previous week as well.

Any help is much appreciated.

Scott

**davesexcel** · 02-25-2011, 04:49 AM

Hi Scott,
It would be beneficial for you to supply a sample workbook for somebody to work with.
Showing how your original data is laid out and then showing what your desired results should be.

**Blake 7** · 02-25-2011, 04:51 AM

EDIT: I also agree with Dave - it would be much more helpful to upload a worksheet.

One way would be to use a helper colum say col c where you put your between dates, in ur eg 10/02/2011 and 20/02/2011

This is a count of mission hourss
=SUMPRODUCT(--($B$1:$B$8>=$C$1), --($B$1:$B$8<=$C$2), $E$1:$E$8)

Please note that you can adjust the array to B1000 and E1000 if required

Where is the count of actual missions?

See the attachment.

**canapone** · 02-25-2011, 05:18 AM

Hi, just an attempt

to count the dates ($B$2:$B$1000 the range, in $C$1 = Thursday Feb 24, the date you have to refer )

=SUMPRODUCT(($B$2:$B$1000>=$C$1-WEEKDAY($C$1)-5)*($B$2:$B$1000<=$C$1-WEEKDAY($C$1)+1))

To sum the hours (same formula integrated by E2:E1000)

=SUMPRODUCT(($B$2:$B$1000>=$C$1-WEEKDAY($C$1)-5)*($B$2:$B$1000<=$C$1-WEEKDAY($C$1)+1)*$E$2:$E$1000)

Regards

**qflyer** · 02-28-2011, 01:42 PM

Originally Posted by CANAPONE

Hi, just an attempt

to count the dates ($B$2:$B$1000 the range, in $C$1 = Thursday Feb 24, the date you have to refer )

To sum the hours (same formula integrated by E2:E1000)

Regards

Perfect, thanks!