Formula to collect information based on year and month

Hugo Martinwall

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

Paul Newcome

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

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)

Hugo Martinwall

@Paul Newcome Thank you very much Paul!

Paul Newcome

Happy to help. 👍️