Count number of times a value shows up over a dynamic date range in Google Sheets

Question

I have a master leads sheet that gets automatically updated when new leads enter the system. The number of leads created on any date varies. Another column for Stage tracks the action step the lead took (SAH Appointment Booked, Store Transfer, Materials, et.).

In another sheet I want to keep track of weekly reporting on total inbound leads, and then the number of instances of the different lead types (stages).

For example, for the week ending date I need to be able to search the dynamic range of total leads generated for that week and return the count of SAH Appointment Booked over that dynamic date range. I got the total inbound leads by using

=COUNTIF(FFLTX!A:A, ">=3/18/2024")-COUNTIF(FFLTX!A:A, "3/24/2024)") to count rows between the date range in the week ending.

How can I acheive this?

I tried some combinations of countif and vlookup but I'm not understanding the nature of using them together.

Master Leads sheet

Weekly Summary Sheet

Answer 1

You may try this in Cell_D3 of your Weekly tabs:

=arrayformula(countifs(weeknum(FFLTX!A:A,2),weeknum(A3,2),K:K,"SAH Appointment Booked"))