Search code examples
excelsumifs

(MS Excel) How do I use sumifs or sumproduct on an array like this that has 3 criteria?

I have tried sumifs and sumproducts to try get the results I need which is to sum the values if the 3 criteria of: column A=prescribed start date, column B=prescribed end date, and the column headers = the person name, is met. Below is the spreadsheet:

Payroll - Extra

This is a sample formula:

=SUMIFS('Payroll - Extra'!C2:C1048576,'Payroll - Extra'!A2:A1048576,"='Payroll Tables and Settings'!S3",'Payroll - Extra'!B2:B1048576,"='Payroll Tables and Settings'!T3",'Payroll - Extra'!C1,'Payslip - Extra'!A3)

The formula is entered in the #VALUE cell in the image below: Payslip - Extra

Sample file

Solution

  • The reason for the VALUE error in your formula is that your third criteria_range is not the same size as the sum_range, which is a requirement for SUMIFS.

    Also, as I mentioned in a comment, you are only SUMming column C (John).

    To use SUMIFS, you need to have the sum_range be the proper column. One way of doing that is by using the INDEX function. Use MATCH to determine the proper column, then enter 0 for the row argument and all of the rows for that column will be returned. See HELP for the INDEX function.

    You should also, as we mentioned in your previous question, remove the cell references from inside the quotes. I made them absolute so they would not increment, but you can change that.

    So one way of rewriting the formula that you have in your screenshot in the cell showing Value is:

    =SUMIFS(INDEX('Payroll - Extra'!$C$2:$J$1048576,0, MATCH($A3,'Payroll - Extra'!$C$1:$J$1,0)),'Payroll - Extra'!A2:A1048576,'Payroll Tables and Settings'!$S$3,'Payroll - Extra'!B2:B1048576,'Payroll Tables and Settings'!$T$3)

    The following should work similarly, and is a bit simpler and shorter:

    =SUMIFS(INDEX('Payroll - Extra'!$C:$J,0, MATCH($A3,'Payroll - Extra'!$C$1:$J$1,0)),'Payroll - Extra'!$A:$A,'Payroll Tables and Settings'!$S$3,'Payroll - Extra'!$B:$B,'Payroll Tables and Settings'!$T$3)