My data set has each days hours logged in a column with the rows being employee logging time. I'm trying to sum a persons hours for a date range in another google sheet, but this formula keeps giving me the error
N/A (Argument must be a range)
The formulas pull up the right ranges when used individually, but error out when combined. I've tried to use Query as well but because of the way the data is set up I can't get that to work either. In the formula below B18 its the key to find the other sheets URL, C18 is the persons name to look for, and E17 and F17 are the date binders.
=sumifs(importrange(Vlookup(B18,$B$3:$O$15,14,0),"Hours!A"&match(C18,Importrange(Vlookup(B18,$B$3:$O$15,14,0),"Hours!A:A"),0)&":AM"&Match(C18,Importrange(Vlookup(B18,$B$3:$O$15,14,0),"Hours!A:A"),0)),Importrange(Vlookup(B18,$B$3:$O$15,14,0),"Hours!$A$1:$AM$1"),">="&F17,Importrange(Vlookup(B18,$B$3:$O$15,14,0),"Hours!$A$1:$AM$1"),"<="&E17)
IMPORTRANGE returns an array of values but SUMIFS arguments should be a range. This means that it's not posible as argument functions that returns arrays like IMPORTRANGE, FILTER, QUERY among other functions.
SUMIFS requires the use ranges which are referred by range references of the form
A1:B2.