Search code examples
dategoogle-sheetssumarray-formulassumifs

Calculate sum if year and weekdate match in the columns

enter image description here

Good afternoon!

I am trying to write a sumifs function that will add up the "Total Gross Income" with the corresponding year and week number

Example

Week Number = 21 && Year = 2021 --> $602.85 ($68.75 + $117.60 + $285.00 + $131.50)

Week Number = 21 && Year = 2022 --> $47.42 ($7.75 + $6.25 + $17.06 + $16.30)

I tried to run this function:

=arrayformula(if(C2="","",sumifs($D$2:$D$9,$C$2:$C$9,C2:C)))

But, it will only return the total sum for the year 2021

Here is a link to a copy of the sheet: https://docs.google.com/spreadsheets/d/1eZbSQD6WxpFyaNw58htAT-d12kHZgIRIFofbdHtN9Ic/edit?usp=sharing

Solution

  • use:

    =INDEX(IFNA(VLOOKUP(B2:B&C2:C, QUERY({B2:B&C2:C, D2:D}, 
 "select Col1,sum(Col2) group by Col1 label sum(Col2)''"), 2, )))

    enter image description here