Sumproduct total value q1,q2,q3,q4 corresponding to the ranges

Forum Legends,

I have a list of value by month from Apr17 to Mar18 . I want to find out the totals by Quarter with multiple criteria.

Please help me.

Originally Posted by JohnTopley

In C5

=SUMIFS('TIME SHEET'!$D$2:$D$366,'TIME SHEET'!$A$2:$A$366,">="&DATE(2017,MONTH(LEFT(C$1,3)&0),1),'TIME SHEET'!$A$2:$A$366,"<="&EOMONTH(DATE(2017,MONTH(RIGHT(C$1,3)&0),1),0),'TIME SHEET'!$B$2:$B$366,"G")

You should now be able to work out the others,

IN C9

=SUMPRODUCT(('TIME SHEET'!$D$2:$D$366),('TIME SHEET'!$A$2:$A$366>=DATE(2017,MONTH(LEFT(C$1,3)&0),1))*('TIME SHEET'!$A$2:$A$366<=EOMONTH(DATE(2017,MONTH(RIGHT(C$1,3)&0),1),0))*(WEEKDAY('TIME SHEET'!$A$2:$A$366,2)=6))

in C10

=SUMPRODUCT(('TIME SHEET'!$D$2:$D$366),('TIME SHEET'!$A$2:$A$366>=DATE(2017,MONTH(LEFT(C$1,3)&0),1))*('TIME SHEET'!$A$2:$A$366<=EOMONTH(DATE(2017,MONTH(RIGHT(C$1,3)&0),1),0))*(WEEKDAY('TIME SHEET'!$A$2:$A$366,2)=7)

In C11

=SUMPRODUCT(('TIME SHEET'!$C$2:$C$366),('TIME SHEET'!$C$2:$C$366<2)*('TIME SHEET'!$A$2:$A$366>=DATE(2017,MONTH(LEFT(C$1,3)&0),1))*('TIME SHEET'!$A$2:$A$366<=EOMONTH(DATE(2017,MONTH(RIGHT(C$1,3)&0),1),0))*(WEEKDAY('TIME SHEET'!$A$2:$A$366,2)<6))

in C12

=SUMPRODUCT(('TIME SHEET'!$C$2:$C$366),('TIME SHEET'!$C$2:$C$366>2)*('TIME SHEET'!$A$2:$A$366>=DATE(2017,MONTH(LEFT(C$1,3)&0),1))*('TIME SHEET'!$A$2:$A$366<=EOMONTH(DATE(2017,MONTH(RIGHT(C$1,3)&0),1),0))*(WEEKDAY('TIME SHEET'!$A$2:$A$366,2)<6))

Copy all across and change year in colmnn F to 2018


You could use helper columns I added in F & G and use SUMIFS for all calculations.

e.g in H5

=SUMIFS('TIME SHEET'!$D$2:$D$366,'TIME SHEET'!$F$2:$F$366,"=" &C$2,'TIME SHEET'!$B$2:$B$366,"G")

Thank you so much for your help and reply. Sorry for he late reply because I am not received any email notification from "Forum".

Formulas working charm. Thank you.

your formula working is fine for eomonths criteria was single cells.(C1=Apr-Jue).

I want the same results but eomonth criteria two columns are C2&C3=April-17 & June-17).

I followup your way and change the edate and emonth ranges but return error. How do resolve the issue.

Please help and advice.

Re yout PM message: try this,,,,

=SUMIFS('TIME SHEET'!$D$2:$D$366,'TIME SHEET'!$A$2:$A$366,">="&C$2,'TIME SHEET'!$A$2:$A$366,"<="&EOMONTH(C$3,0),'TIME SHEET'!$B$2:$B$366,"G")