• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Status
Not open for further replies.

Stefan Deckers

New Member
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
 

Attachments

  • Number of times code occurs per week in 2020.xlsx
    245.1 KB · Views: 9
Using Power Query, here is the Mcode generated by the steps taken. File attached for your review

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FormatDate = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Dates", type date}, {"Code", type text}, {"Week number", Int64.Type}, {"Year", Int64.Type}}),
    RemoveNullRows = Table.SelectRows(FormatDate, each ([Dates] <> null)),
    RemovedColumns = Table.RemoveColumns(RemoveNullRows,{"Column1", "Dates"}),
    GroupedByYearByWeek = Table.Group(RemovedColumns, {"Year", "Week number", "Code"}, {{"Count", each Table.RowCount(_), type number}}),
    SortedRows = Table.Sort(GroupedByYearByWeek,{{"Year", Order.Ascending}, {"Week number", Order.Ascending}})
in
    SortedRows
 

Attachments

  • Number of times code occurs per week in 2020 (1).xlsx
    249.3 KB · Views: 5
You can no longer use COUNTIFS if you want to avoid listing the week numbers and years, because the 'xxxIFS' functions requires Range References and not Arrays. In place of that you could use SUM or SUMPRODUCT
= SUM( (YEAR(DATA[Dates])=YEAR(aDate)) * (WEEKNUM(+DATA[Dates])=WEEKNUM(aDate)) * (DATA[.Code]=aCode) )

Note: WEEKNUM is happy with arrays but will not accept a multi-cell range, hence the "+"

Because I use array formulas for all calculation, I would prefer to stick with helper ranges, even if they were contained within a hidden sheet, so that multiple results could be output using a single array formula.
 
Dear,

Purfleet gave me this formula;
=COUNTIFS(DATA!$C:$C;'# Codes'!B$2;DATA!$B:$B;">="&DATE($Q$1;1;1-WEEKDAY(DATE($Q$1;1;1);2))+$A19*7;DATA!$B:$B;"<="&DATE($Q$1;1;1)-WEEKDAY(DATE($Q$1;1;1);2)+$A20*7-1)

71312

I've been using the formula for some time now.

Thanks for the help.

Sincerely,

Stefan
 
Status
Not open for further replies.
Back
Top