I am a relatively unskilled user of Excel at the moment. I have a column of
dates where I would like to count the number of occurrences within a week
i.e. between 2 dates. Say I have a set of dates spread over 1 month; I want
to end up with 4 separate figures for each week of that month. Is this
possible in one formula? So far I have resorted to sorting the column in
date order and created separate Sum fields for each of the 4 weeks but this
seems unweilding and I have to do the same thing on multiple sheets within a
workbook.

Hi,

You don't say what it is you want to sum. the dates thenselve? another
column? so I assume the adjacent column.

=SUMPRODUCT((A1:A30=C1)*(A1:A30<=C2)*(B1:B30))

Wher C1 is the start date, C2 is the end date and column B is what you want
to sum.

Mike

"tartanspice" wrote:

I am a relatively unskilled user of Excel at the moment. I have a column of
dates where I would like to count the number of occurrences within a week
i.e. between 2 dates. Say I have a set of dates spread over 1 month; I want
to end up with 4 separate figures for each week of that month. Is this
possible in one formula? So far I have resorted to sorting the column in
date order and created separate Sum fields for each of the 4 weeks but this
seems unweilding and I have to do the same thing on multiple sheets within a
workbook.

Hi

With dates in column A, start / end date in B1:B2 try this formula:

=SUMPRODUCT(--(A1:A100=B1);--(A1:A100<=B2))

Regards,
Per

"tartanspice" skrev i meddelelsen
...
I am a relatively unskilled user of Excel at the moment. I have a column of
dates where I would like to count the number of occurrences within a week
i.e. between 2 dates. Say I have a set of dates spread over 1 month; I
want
to end up with 4 separate figures for each week of that month. Is this
possible in one formula? So far I have resorted to sorting the column in
date order and created separate Sum fields for each of the 4 weeks but
this
seems unweilding and I have to do the same thing on multiple sheets within
a
workbook.

If you just want to count them you can omit the (B1:B30) term in
Mike's formula.

Pete

On Oct 6, 11:43*am, Mike H wrote:
Hi,

You don't say what it is you want to sum. the dates thenselve? another
column? so I assume the adjacent column.

=SUMPRODUCT((A1:A30=C1)*(A1:A30<=C2)*(B1:B30))

Wher C1 is the start date, C2 is the end date and column B is what you want
to sum.

Mike



"tartanspice" wrote:
I am a relatively unskilled user of Excel at the moment. I have a column of
dates where I would like to count the number of occurrences within a week
i.e. between 2 dates. *Say I have a set of dates spread over 1 month; I want
to end up with 4 separate figures for each week of that month. Is this
possible in one formula? *So far I have resorted to sorting the column in
date order and created separate Sum fields for each of the 4 weeks but this
seems unweilding and I have to do the same thing on multiple sheets within a
workbook.- Hide quoted text -

- Show quoted text -

Many Thanks Mike and Pete for your help. I tried Pete's amendment to Mike's
formula since I do just want to count but it returned a zero result. I even
tried putting quotes round the dates at C1 and C2 but that gave an error.
Perhaps if I'm more specific on the problem.

I have a column containing dates in a random order and I need to get weekly
totals so that if there are say, 50 dates ranging from 01/04/08 to 30/09/08
(UK format) I have to be able to say that there are 10 in the week ending 30
April, 25 in w.e. 15 June and 15 in w.e. 30 September, for example, for each
week in that range as necessary. The workbook is accessed by other users who
could alter any sorting I may do in date order which is why I can't use the
simple sort and sum method I had initially tried. Also I have to do this
over several worksheets ending up with a single count for the same week from
all sheets.

I did try a few other suggestions from other threads before I posted my
query but none seemed to foot the bill.

Many thanks in advance,
Anne


"Pete_UK" wrote:

If you just want to count them you can omit the (B1:B30) term in
Mike's formula.

Pete

On Oct 6, 11:43 am, Mike H wrote:
Hi,

You don't say what it is you want to sum. the dates thenselve? another
column? so I assume the adjacent column.

=SUMPRODUCT((A1:A30=C1)*(A1:A30<=C2)*(B1:B30))

Wher C1 is the start date, C2 is the end date and column B is what you want
to sum.

Mike



"tartanspice" wrote:
I am a relatively unskilled user of Excel at the moment. I have a column of
dates where I would like to count the number of occurrences within a week
i.e. between 2 dates. Say I have a set of dates spread over 1 month; I want
to end up with 4 separate figures for each week of that month. Is this
possible in one formula? So far I have resorted to sorting the column in
date order and created separate Sum fields for each of the 4 weeks but this
seems unweilding and I have to do the same thing on multiple sheets within a
workbook.- Hide quoted text -

- Show quoted text -