Adding function to Formula

Occasional Visitor

I have used the following formular which works however I need to add a second criteria and this is where I have issues. I have selected the column than I have the two dates below so that I can change them to get the data I want (B125 and B126) and I want it to tell me how many with those dates had Saturday. However I want to expand this to a certain neighborhood and I cant figure it out. 

=COUNTIFS(Sheet1!C3:C400,">="&Sheet2!B125,Sheet1!C3:C400,"<="&Sheet2!B126,Sheet1!E3:E400,"Saturday")

 

Example:  Currently, I have 10 rows of information Neighborhood, Date, Day of Week, and the game they played. I created a cell so that I can change the dates and just look at 1/15/22 to 1/22/22 if I want without having to change my formula. I know how many participated between the dates on Saturday(above formula) total but I want to now see how many participated on Saturday that lived on House Lane. 

 

Hope this makes sense. 

1 Reply
Hello, you can use sumifs or sumproduct.
Let's assume for your easy understanding as below.
List of People lived on Specific Lane=Sheet1! G3:G400
List of Lane=Sheet1!F3:F400.
House Lane = HL
You may change accordingly.

The formula using Sumifs is as below.
=SUMIFS(
Sheet1! G3:G400,
Sheet1! F3:F400, HL, Sheet1!C3:C400,">="&Sheet2!B125, Sheet1!C3:C400,"<="&Sheet2!B126, Sheet1!E3:E400,"Saturday")

The formula using Sumproduct is as below.
=SUMPRODUCT(
Sheet1! G3:G400*
(Sheet1! F3:F400=HL)*(Sheet1!C3:C400>=Sheet2!B125)*(Sheet1!C3:C400<=Sheet2!B126)*(Sheet1!E3:E400,"Saturday"))