Sumifs, Index, and Match with multiple criteria

[SnKeaK]

Worksheet A Worksheet B

Hi, I am asking for assistance putting together a formula that can:

1. Search WS-A's A Column for current date and Match the User in Row 1 with Worksheet B's Column A, and input the current days data in the in Column B.
Formula will be in Column B on Worksheet B.

2. Same thing as the above, but this time I need a total count of the previous 7 days total.
Formula will be in Column C on Worksheet B.

 

[Peter_SSs]

I have assumed the sheet names are actually 'A' and 'B'
In B2 of sheet 'B'

=SUMIFS(INDEX(A!B$3:J$100,0,MATCH(A2,A!B$2:J$2,0)),A!A$3:A$100,TODAY())

In C2 of Sheet B

=SUMIFS(INDEX(A!B$3:J$100,0,MATCH(A2,A!B$2:J$2,0)),A!A$3:A$100,"<="&TODAY(),A!A$3:A$100,">"&TODAY()-7)

 

[SnKeaK]

I have assumed the sheet names are actually 'A' and 'B'
In B2 of sheet 'B'

=SUMIFS(INDEX(A!B$3:J$100,0,MATCH(A2,A!B$2:J$2,0)),A!A$3:A$100,TODAY())

In C2 of Sheet B

=SUMIFS(INDEX(A!B$3:J$100,0,MATCH(A2,A!B$2:J$2,0)),A!A$3:A$100,"<="&TODAY(),A!A$3:A$100,">"&TODAY()-7)

https://1drv.ms/x/c/f7b9915682722e86/EQa4FmNLK7xEnVexYmVntocBYp6VwJ4k4Qq9uN7YmhIJNQ?e=7ZnBpN

I'm getting a bunch of #N/A's. This is the link to my worksheets so you can what i'm doing wrong.

 

[Peter_SSs]

Three things

1. Your data goes way beyond column J that I had used in my formula. The Js need to be changed to include the whole data range
2. I had missed that rows 1 and 2 in 'A' are merged in each column so the MATCH part of the formulas should be looking at 'A' row 1, not row 2
   MATCH(A2,A!B$1:LastColumnofdata$1,0)
3. The workbook has so many volatile functions (eg INDIRECT) that it takes forever to recalculate on my machine so I gave up waiting.

 

[SnKeaK]

I have assumed the sheet names are actually 'A' and 'B'
In B2 of sheet 'B'

=SUMIFS(INDEX(A!B$3:J$100,0,MATCH(A2,A!B$2:J$2,0)),A!A$3:A$100,TODAY())

In C2 of Sheet B

=SUMIFS(INDEX(A!B$3:J$100,0,MATCH(A2,A!B$2:J$2,0)),A!A$3:A$100,"<="&TODAY(),A!A$3:A$100,">"&TODAY()-7)

After making the necessary changes you mentioned, it works like a charm. Thank you for your time.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.