Formula to collect information based on year and month
Hello!
We have a sheet where we put down information from all the invoices we send.
There are two collumns I want to collect data from. Date and "hours spent".
I used: =SUMIF({Service - Fakturering Range 4}; MONTH(@cell) = 3; {Service - Fakturering Range 5}) to get out how many hours we´ve spend in Mars.
The problem occurs when this sheet and formula is used for more than a year because now it collects the hours invoiced in Mars 2020 and 2021.
Based on this I have one main question and a following qustion;
- Does anyone know how to correct this formula to only search for year 2020 and month 3 instead of only month 3?
- We also have a third column with different "Customer ID:s". When the formula can collect the correct amount of hours spend in total in Mars 2020. Can it also sort out hours spend in mars 2020 based on customer ID in a third column?
Kind Regards,
Hugo Martinwall
Best Answer
-
For month and year specific, try this one...
=SUMIFS({Service - Fakturering Range 5}; {Service - Fakturering Range 4}; AND(IFERROR(MONTH(@cell); 0) = 3; IFERROR(YEAR(@cell); 0) = 2021))
And to incorporate the customer ID:
=SUMIFS({Service - Fakturering Range 5}; {Service - Fakturering Range 4}; AND(IFERROR(MONTH(@cell); 0) = 3; IFERROR(YEAR(@cell); 0) = 2021); Other Sheet Customer ID Column}; [Customer ID]@row)
Answers
-
For month and year specific, try this one...
=SUMIFS({Service - Fakturering Range 5}; {Service - Fakturering Range 4}; AND(IFERROR(MONTH(@cell); 0) = 3; IFERROR(YEAR(@cell); 0) = 2021))
And to incorporate the customer ID:
=SUMIFS({Service - Fakturering Range 5}; {Service - Fakturering Range 4}; AND(IFERROR(MONTH(@cell); 0) = 3; IFERROR(YEAR(@cell); 0) = 2021); Other Sheet Customer ID Column}; [Customer ID]@row)
-
@Paul Newcome Thank you very much Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 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!