SumIFs with an Index Match

[mcrich09]

Good day, I've searched and have found a handful of post, but every time I try to go with what is suggested I get an error or the new SPILL. I have sheet one which is the input data and I'm trying to show on sheet two the % of work each person has for that week to load a visual. I've used SUMIF and get the wrong number and SUMIFS returns errors. Can you please help me in the right direction on what I'm doing wrong?

=SUMIF(Sheet1!$D$2:$D300,">"&$C$1,INDEX(Sheet1!A$1:AZ3000,MATCH($A2,Sheet1!A$1:AY$1,0),0))

=SUMIFS(INDEX(Sheet1!A1:AZ3000,MATCH(A2,Sheet1!A1:AY1,0),0),Sheet1!D$2:D300,">"&D1,Sheet1!F2:F300,"="&"WORKING")

I couldn't figure out how to upload the Excel file...

Thanks!

 

[alz]

you can upload the files at dropbox and share the link here!

Some of Start Date and Finished Date have no entry in your Data . Which Date is represented for that row?
For 1/4/2021 JV what is your actual expected Answer?

 

[mcrich09]

Hi ALZ, I didn't see a link, but I'm expecting to get 64.5%. Thanks!

 

[alz]

I want to look your excel file for better understanding .You can upload at this website => www.dropbox.com , GoogleDrive or OneDrive & insert Link here

 

[Fluff]

@alz we prefer members to use the XL2BB add-in rather than posting files on a share site.

@mcrich09
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[mcrich09]

Fluff - I'm unable to load the APP. IS it possible to use the share drive or is it stritcly frowned on? Thanks

 

[Fluff]

You can use share sites as long as you post the link to the thread, we just prefer members to use the add-in where possible.

Looking at you two formulae the index is returning 3000 rows, but the other ranges are only 300. They need to be the same size.