How can I count per year and per week the total number of times a code occurs

Stefan_Deckers · #1 06-25-2020, 11:32 AM

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

Purfleet · #2 06-26-2020, 09:26 PM

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

Pecoflyer · #3 06-27-2020, 09:35 AM

Cross post How can I count per year and per week the total number of times a code occurs | Chandoo.org Excel Forums - Become Awesome in Excel

Stefan_Deckers · #4 09-19-2020, 02:36 PM

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