SUMIF amount if name is found and is in the last 6 days giving incorrect total

Afternoon all hope someone can help.

I am trying to get the totals for 6 names on Tab "Live Snapshot" Columns E-J Row 3.
I Have told it to look on the "Cohorts" Tab to find the name in Column B, for the example i've attached it is "Apprenticeships" and give the total number of learners from Column E but only if the Date in Column A is within 6 days of today(the current day).

I have tried this. =SUMIFS(Cohorts!$E:$E,Cohorts!$B:$B,J2,Cohorts!$A:$A,">="&(TODAY()-6))

However the formula I have made is wrong as it is including future dates when adding up the total. It is saying 11 for Apprenticeships when it should be 7. Could someone kindly point out where I have messed up please?

I have attached an example spreadsheet for you to see.

Thank you for any help you may be able to give.

Try this in E3:

=SUMIFS(Cohorts!$E:$E,Cohorts!$B:$B,E2,Cohorts!$A:$A,">="&(TODAY()-6),Cohorts!$A:$A,"<="&TODAY())

Drag to the right.

Wow that was fast! Thank you so much that did the trick!

First time using the TODAY function so I need to practice on that a bit more.

Thanks again

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Oh, and welcome to the forum!

Yes all taken care off. Will close the thread.

Thank you!