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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!