countif - amount of damages per week / month
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
Best Answer
-
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) = ####))
Answers
-
Hi @Jörg Schmidt Novum Hospitality ,
create a helper column for week and month with the following 2 formulas
use "WEEKNUMBER"
and "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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
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
-
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) = ####))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!