We are trying to count how many applicants applied in the last 7 days for Delta client

Louis.Smith

The formula we used is :

=COUNTIFS([Avature Application]:[Avature Application], Client:Client, "Delta", @cell <= TODAY(), [Avature Application]:[Avature Application], @cell > TODAY(-7))

Once we get it for this sheet, we want to do the same for a few sheets and add them all together to know the total. we use this sheet as the start sheet but applicants move to other sheets

Paul Newcome

Your syntax is off. Make sure you are following the appropriate syntax of

=COUNTIFS(range1, criteria 1, range 2, criteria 2, range 3, criteria 3)

Louis.Smith

Perfect that worked.

=COUNTIFS([Avature Application]:[Avature Application], @cell <= TODAY(), Client:Client, "Delta", [Avature Application]:[Avature Application], @cell > TODAY(-7))

Now I am trying to pull that formula into another sheet but its not working see the below:

=COUNTIFS({NSLI Application Range 1}:{NSLI Application Range 1}, @cell <= TODAY(), {NSLI Application Range 2}:{NSLI Application Range 2}, "Delta",{NSLI Application Range 1}:{NSLI Application Range 1}, @cell > TODAY(-7))

Paul Newcome

When using cross sheet references, the {Reference} is the range. You do not have to enter it twice since the {reference} is already pointing at the entire column

Same Sheet:

[Column Name]:[Column Name]

Different Sheet:

{Range Name}