countif - amount of damages per week / month

Joerg Schmidt

Hallo,

I want to show the amount of damages per week / month.

Do you have an idea for a formula ?

I use "Schadeneintritt" (=date of damage) with date as column type

Paul Newcome

You do not need helper columns. You can leverage the date column you already have by nesting the WEEKNUMBER and MONTH functions directly in the COUNTIFS.

=COUNTIFS(Schadeneintritt:Schadeneintritt, IFERROR(WEEKNUMBER(@cell), 0) = #)

=COUNTIFS(Schadeneintritt:Schadeneintritt, IFERROR(MONTH(@cell), 0) = #)

If you have multiple years built into the data sheet, you can incorporate a YEAR function as well.

=COUNTIFS(Schadeneintritt:Schadeneintritt, AND(IFERROR(WEEKNUMBER(@cell), 0) = #, IFERROR(YEAR(@cell), 0) = ####))

=COUNTIFS(Schadeneintritt:Schadeneintritt, AND(IFERROR(MONTH(@cell), 0) = #, IFERROR(YEAR(@cell), 0) = ####))

Stefan

Hi @Jörg Schmidt Novum Hospitality ,

create a helper column for week and month with the following 2 formulas

use "WEEKNUMBER"

https://help.smartsheet.com/function/weeknumber

and "MONTH"

https://help.smartsheet.com/function/month

and use the values in your COUNTIF with the weeknumber or month from "TODAY".

Something like

=COUNTIF(week:week, =WEEKNUMBER(TODAY()))

Hope this helps

Stefan

Joerg Schmidt

this shows the amount of damages in the current week.

Any idea how I can show the number of damages in week1, week2, and so on... ;-)

Thank you

Paul Newcome

You do not need helper columns. You can leverage the date column you already have by nesting the WEEKNUMBER and MONTH functions directly in the COUNTIFS.

=COUNTIFS(Schadeneintritt:Schadeneintritt, IFERROR(WEEKNUMBER(@cell), 0) = #)

=COUNTIFS(Schadeneintritt:Schadeneintritt, IFERROR(MONTH(@cell), 0) = #)

If you have multiple years built into the data sheet, you can incorporate a YEAR function as well.

=COUNTIFS(Schadeneintritt:Schadeneintritt, AND(IFERROR(WEEKNUMBER(@cell), 0) = #, IFERROR(YEAR(@cell), 0) = ####))

=COUNTIFS(Schadeneintritt:Schadeneintritt, AND(IFERROR(MONTH(@cell), 0) = #, IFERROR(YEAR(@cell), 0) = ####))