Forum Discussion

GMinch's avatar
GMinch
Copper Contributor
Aug 25, 2023

SUMIF Multiple Cells Across Multiple Worksheets

I have a very simple spreadsheet where I am trying to total the number of hours spent on a project for a given week. I am using a SUMIF function to attempt to add hours for a particular job together, if that job appears on the timesheet for any given employee. 

 

=SUMIF(Tyler:John!$A$5:$A$14,A4,Tyler:John!$J$5:$J$14) - Returns (#Value!) Error

 

The formula works If I limit it to any one worksheet.

=SUMIF(Tyler!$A$5:$A$14,A4,Tyler!$J$5:$J$14) - Returns sum for all Tylers hours matching criteria

 

Do you have any suggestions on how to accomplish this?

 

 

 

 

 

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    GMinch 

    SUMIF does not accept 3D references.

     

    If you have 365, you could stack the data with VSTACK then sum.

    =LET(
        stack, VSTACK(Stack3D),
        project, TAKE(stack, , 1),
        hours, TAKE(stack, , -1),
        Total, LAMBDA(ID, SUM(FILTER(hours, project = ID, 0))),
        BYROW(ProjectID, Total)
    )

      

Resources