Search code examples
google-sheets-formulasumifs

Google Sheets: Help excluding data from SUMIFS

I am using the following formula to sum the total number of hours in the 'Time Sheets' sheet between 2 dates:

=SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023")

enter image description here

This is working but I would also like to be able to add a filter so that I can exclude anything from column A with certain codes (eg PP999, PP998 etc.) within the same date range.

enter image description here

I have been using the formula below as a work round however the formula is becoming rather long and it is limiting how I can use it, can anyone help with this?

=SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023")-SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!A2:A,"PP999",'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023")-SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!A2:A,"PP998",'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023")-SUMIFS('Time Sheet'!$K$2:$K,'Time Sheet'!A2:A,"PP997",'Time Sheet'!G2:G,">=01/12/2022",'Time Sheet'!G2:G,"<=30/11/2023")
Solution

  • Here's one approach you can try out:

    =sum(ifna(filter('Time Sheet'!K2:K,'Time Sheet'!G2:G>=date(2022,12,1),'Time Sheet'!G2:G<=date(2023,30,11),iserror(xmatch('Time Sheet'!A2:A,{"PP997","PP998","PP999"})))))