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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!