SUMIFS with multiple criteria(Dates/Names) from various worksheets

[dhubz]

Hello,

I have a raw CSV file that is built from another program. I need to extract data from the information to generate employee work records. See attached images
I am trying to sum a specific range based on 5 variables - Name, Contract, After Date, Before Date,(Not in formula- check whether it's Lead/Helper/Split)
The Employee cell is from a drop down list, the CSV Data is copy and paste from another file.

This is the formula I have tried, I always get a #VALUE ERROR
SUMIFS('RAW DATA'!F:H,'RAW DATA'!D:D,Tally!$B$1,'RAW DATA'!B:B,Tally!$B$3,'RAW DATA'!C:C,">"&Tally!$I$1,'RAW DATA'!C:C,"<"&Tally!$L$1)

GOOGLE DRIVE FILE HERE
Any tips or pointers would be greatly appreciated,
Dave

 

[jasonb75]

All ranges in SUMIFS or any similar function must be of equal size. Your sum range is 3 columns, but your criteria ranges are only 1 column each.

See if this does what you need

=SUM(SUMIFS(OFFSET('RAW DATA'!$F:$F,,{0,1,2}),'RAW DATA'!$D:$D,Tally!$B$1,'RAW DATA'!$B:$B,Tally!B$3,'RAW DATA'!$C:$C,">"&Tally!$I$1,'RAW DATA'!$C:$C,"<"&Tally!$L$1))

OFFSET is used to create 3 single columns in the sum range, which are then tallied by wrapping the entire formula in SUM

 

[dhubz]

All ranges in SUMIFS or any similar function must be of equal size. Your sum range is 3 columns, but your criteria ranges are only 1 column each.

See if this does what you need

=SUM(SUMIFS(OFFSET('RAW DATA'!$F:$F,,{0,1,2}),'RAW DATA'!$D:$D,Tally!$B$1,'RAW DATA'!$B:$B,Tally!B$3,'RAW DATA'!$C:$C,">"&Tally!$I$1,'RAW DATA'!$C:$C,"<"&Tally!$L$1))

OFFSET is used to create 3 single columns in the sum range, which are then tallied by wrapping the entire formula in SUM

That makes sense, thanks for the help. I'm sure I will be back.