This may be a tough one, good luck. - Thanks in advance!
IFS(SUMIFS($G3:$G40, $B3:$B40, "READING", $C3:$C40, "COMPLETE"), IFS(M2=2018, SUMIFS($G3:$G40,$E3:$E40,">="&"1/1/2018",$E3:$E40,"<="&"12/31/2018"), M2=2019, SUMIFS($G3:$G40,$E3:$E40,">="&"1/1/2019",$E3:$E40,"<="&"12/31/2019"), M2=2020, SUMIFS($G3:$G40,$E3:$E40,">="&"1/1/2020",$E3:$E40,"<="&"12/31/2020")))
^what I have now^
I want a formula that depends on a Data Validation list of 2018, 2019, 2020.
When one of those values is selected in the drop down the formula is supposed to validate 3 conditions.
1st condition: category column has "reading" selected in the row
2nd condition: status column has "complete" selected in the row
3rd condition: date column falls within the year selected in the row
i.e. (when 2018 is selected in cell $M2 and if the category column is "reading' in row 3 and status column is "complete" in row 3 then it should check if the date column in row 3 is between 1/1/2018 and 12/31/2018 ... and if all is true then it would go to the pages column and sum the value in row 3) [respectively if row 4 had those conditions fulfilled the formula would sum row 3 pages' column value and row 4 pages' column value]
haaalp.PNG
haaalp_2.PNG
Bookmarks