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

[silambarasan.J]

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.

[JohnTopley]

In C5

Please Login or Register  to view this content.

You should now be able to work out the others,

IN C9

Please Login or Register  to view this content.

in C10

Please Login or Register  to view this content.

In C11

Please Login or Register  to view this content.

in C12

Please Login or Register  to view this content.

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")

[silambarasan.J]

In C5

Please Login or Register  to view this content.

You should now be able to work out the others,

IN C9

Please Login or Register  to view this content.

in C10

Please Login or Register  to view this content.

In C11

Please Login or Register  to view this content.

in C12

Please Login or Register  to view this content.

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.

[JohnTopley]

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")