Counting by Week and Month

**Journeyman3000** · 02-10-2014, 07:36 PM

Hi all,

Please see my attached sheet. Forum Demo.xlsx

I am counting activities that occur on a daily basis. I need to know for each week and month how often these activities occur

I am currently using a basic =Sum(Cell:Cell), but this cannot work. My dates are displayed dynamically. i.e shows only activities for the past month (31 days), which throws out the sums when counting down the columns.

Can someone please review and advise the best way of tackling this, and suggest a formula that might be useful. Note that a chart is the final result.

Cheers

**daffodil11** · 02-10-2014, 08:04 PM

For week, I went with this in E2:

=IF(AND(B2<>B3,B3<>""),SUMIF($B$2:$B$33,B2,$D$2:$D$33),"")

and copied down

For month, I did this in F2:

=IF(AND(MONTH(A33)&YEAR(A33)<>MONTH(A34)&YEAR(A34),MONTH(A34)&YEAR(A34)<>""),SUMPRODUCT((MONTH(A33)&YEAR(A33)=MONTH($A$2:$A$33)&YEAR($A$2:$A$33))*($D$2:$D$33)),"")

and copied down

In your example, you're summing the weeknum and not the count amounts, so I was a little thrown off. I summed D. Change D as needed.

**Journeyman3000** · 02-10-2014, 08:22 PM

Hi Daffodil and thanks for your very fast response.

The week formula works perfectly. Brilliant.

The month formula however doesn't seem to work correctly. I copied your formula in to F2, filled down and the end result was 18 in F2.

I changed the starting dates but this only changed F2. Note that the dates wen over 2 months - part way through Jan, part way through Feb.

Am I missing something?

Thanks again for your assitance with this.

**daddylonglegs** · 02-10-2014, 08:45 PM

Perhaps try this formula for F2 copied down

=IF(DAY(A2+1)=1,SUMIF(A$2:A2,">"&A2-DAY(A2),D$2:D2),"")

**Journeyman3000** · 02-10-2014, 08:47 PM

Awesome. Take a rep and thanks very much. Works perfectly!

Counting by Week and Month

LinkBack

Thread Tools

Rate This Thread

Display

Counting by Week and Month

Re: Counting by Week and Month

Re: Counting by Week and Month

Re: Counting by Week and Month

Re: Counting by Week and Month

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Showing Fridays of each month in a 4 week month and a five week month

[SOLVED] Counting how many dates in a range fall into this week and last week

Need to find the week number of the month, first of the month not always the 1st week

Counting a Football Team's Record Week by Week

Counting dates that fall in a specific week of the month

Bookmarks

Bookmarks

Posting Permissions