Search code examples
excelsumifs

Sumif Excel Function Error

I'm trying to calculate the days that an employee had an overtime; but the function keeps give an error function. I tried after searching use (;) instead of (,); but still keep getting the error.

The function:

=SUMIFS(LeaveTracker[Days],LeaveTracker[Employee Name],valSelEmployee,WEEKDAY(LeaveTracker[Start Date]),">5",WEEKDAY(LeaveTracker[End Date]),">5",LeaveTracker[Type of Leave],'Leave Types'!B8)

The Error message: The formula you typed contains an error.

Solution

  • As I wrote in my comment, criteria_range arguments need to be ranges. You have two which are not: WEEKDAY(LeaveTracker[Start Date]) and WEEKDAY(LeaveTracker[End Date]) do not return a range. They returns an array of weekday numbers. Hence your error.

    You could add two helper columns containing just the Weekday number referenced to start and end date, and then use those columns for the criteria_range in place of what you have.

    So add a column named StartWeekDay with the formula: =WEEKDAY([@[Start Date]]) and similarly add a column named EndWeekDay

    Then you can use SUMIFS:

    =SUMIFS(LeaveTracker[Days],LeaveTracker[Employee Name],valSelEmployee,(LeaveTracker[StartWeekDay]),">5",(LeaveTracker[EndWeekDay]),">5",LeaveTracker[Type of Leave],'Leave Types'!B8)

    Or you can use the SUMPRODUCT function, which can perform that logic on your existing data with no need for helper columns.

    =SUMPRODUCT(LeaveTracker[Days]*(LeaveTracker[Employee Name]=valSelEmployee)*(WEEKDAY(LeaveTracker[Start Date])>5)*(WEEKDAY(LeaveTracker[End Date])>5)*(LeaveTracker[Type of Leave]='Leave Types'!B8))