|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I count per year and per week the total number of times a code occurs
Dear,
How can I count per year and per week the total number of times a code occurs => only using column “Date” and column “Code”; so without extra “helper”- columns “Weeknumber” & “Year”. My idea is to use only a formula, so without using pivot table, etc. Thank you in advance, Stefan |
#2
|
|||
|
|||
Its not a nice formula and the help columns are much better in this case, but i think the attach works
Code:
=COUNTIFS(DATA!$C:$C,'# Codes'!W$2,DATA!$B:$B,">="&DATE($AL$1,1,1-WEEKDAY(DATE($AL$1,1,1),2))+$V2*7,DATA!$B:$B,"<="&DATE($AL$1,1,1)-WEEKDAY(DATE($AL$1,1,1),2)+$V3*7-1) Also i have unmerged AL1:AM1 - nothing good will ever come of merged cells and they should be avioded in my opinion |
#3
|
||||
|
||||
|
#4
|
|||
|
|||
Dear,
I've been using the formula for some time now. Thanks for your help. Sincerely, Stefan |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do you count how often a specific value occurs in single changing cell? | blackjack | Excel | 12 | 09-03-2014 10:41 AM |
Adding of times total to be in hours | jen0dorf | Excel | 4 | 06-20-2014 11:53 PM |
Formula to count entries as per month, year and submission wise from different sheets | pinkacidpunk | Excel | 1 | 05-28-2013 08:26 AM |
count how many tims a certain value occurs | lucnijs | Excel | 6 | 03-26-2012 09:29 AM |
How to count year lapse (rounded off) based on specific date | KIM SOLIS | Excel | 1 | 11-01-2011 10:50 AM |