Sum between date range

[BIGGAZ]

Hi,

I have a spreadsheet of data which includes date and day. I have a summary page that i would like to have the consolidated results of weekly jobs on.

Data page is like below

DATE	DAY	MACHINE	NAME	STITCHES	RUNS	COMPLETED	TOTAL STITCHES
11/1	TUESDAY	8 HEAD	JOE ELECTRICS	8000	3	22	176,000
11/1	TUESDAY	8 HEAD	CAKEBIZ	3500	6	48	168,000
11/1	TUESDAY	SINGLE HEAD	LUSHIOUS	4800	5	35	168,000
12/1	WEDNESDAY	SINGLE HEAD	SCOTT FREE	1700	6	48	81,600

Summary page

DATE		SINGLE HEAD		6 HEAD		8 HEAD
START	END	COMPLETED	STITCHES	COMPLETED	STITCHES	COMPLETED	STITCHES
9/1	13/1	93	249,600	-	-	70	344,000

I need a formula to go in C3 and onwards to will look at the data spreadsheet see if the date falls between the dates listed in A3 and B3 and is "single head" then add those together.

Secondly I need a formula for an average on the same summary page. Combine all the Tuesdays of 1 date on each machine then average that number against other Tuesday on the same machine. So i can predict what we can get through on each given day.

I have been told this can be done with power query but I would prefer a formula as others will be viewing this and teaching them to update PQ each time is an issue its self.

 

[BIGGAZ]

Hi,

I have figured out part of the question with this formula.

=IF(TODAY()<=B13,"",SUMIFS(DATA!$H$2:$H$2000,DATA!$D$2:$D$2000,"SINGLE HEAD",DATA!$B$2:$B$2000,">="&B13,DATA!$B$2:$B$2000,"<="&C13))

Still need a solution for the averages.