SUMIF when Sum and Criteria ranges have different number of rows?

The issues I am seeing is on he orig sheet. However, the "solution" carries over. You already have the spreadsheet set up to handle this; you just have the wrong formula. In the Cost/Week Column, you should have =IFERROR(VLOOKUP(A3,Table2,2,FALSE)*VLOOKUP(A3,Table2,3,FALSE),0)

Where Table2 is an Excel table containing the information on the Table Sheet. I tend to use tables since they know how big they are and they are easier to reference in formulas. The VLOOKUPS don't care how big the range for the lookup is nor the order in which the names are. I looked up the FTE and the rate and multiplied them together to get the cost. I hope that's the correct calculation.

I wrapped the formula in an IFERROR statement because you have a couple of people who are on the schedule but not on the list.

Now you have an even number of rows for the SUMIFS: =SUMIFS(I3:I65,D3:D65,"<>OFF").

The VLOOLUPS bring the information in from the Table Sheet into the correct number of rows. There is no need to try to go cross-page with the SUMIFS.

Array formulas - and that's what SUMIF, SUMIFS, COUNTIF, etc. are in disguise even if you don't enter them with CTRL-SHIFT_ENTER - need the same sized ranges. Normally, they are on the same sheet and in different columns, but they don't have to be.

So you need to come up with two ranges that are the same size. You are working on COST on the Orig sheet, so you will need to work with the same number of rows. You have three more rows on the Orig sheet than you have with the Table sheet. As it turns out, it's three people who are not on the Table sheet.

So we look up everyone from the Table sheet using VLOOKUP - VLOOKUP doesn't care how many names are on the Table sheet nor what order they might be in. In this case we have a match for every person except the three that aren't on the list. With just plain VLOOKUP, the returned data for these people would be #N/A. So we wrap the VLOOKUP in an IFERROR statement so when someone isn't found, it returns zero.

We have now created a column of data (Column I) that has as many rows as Column D does. We can use these two columns in a SUMIF or SUMIFS formula.

Vlookup is just a special case of INDEX / MATCH where the what you are looking up on happens to be in the first column of where you are looking.