Hello, Please help and thank you in advance!!
I have a formula to sum values "TimeSheet!E:E" according to month selected "AC2" and by ID & Emp, but what I have been struggling with is the other part of the formula if a month is not selected and a quarter is selected.
Instead of months as text, I use 1-12 to simplify formula. This is the formula that I am using.
=SUM((MONTH(TimeSheet!$G:$G)=$AC$2)*(TimeSheet!$B:$B=$B5)*(TimeSheet!$J:$J=E$2)**(TimeSheet!$E:$E))
AC2 (Month 1-12/1Qtr, 2Qtr, 3Qtr, 4Qtr) B:B (Emp Column) E:E (Miles Column) G:G (Dates Column) J:J (ID Column)
The formula I have been trying is:
SUM(SUMIF(AC2,{"1","2","3","4","5","6","7","8","9","10","11","12"},TimeSheet!$J:$J=C$2,TimeSheet!$B:$B=$B5,(MONTH(TimeSheet!$G:$G)=$AC$2,TimeSheet!$E:$E),SUMPRODUCT((ROUNDUP(MONTH(TimeSheet!$G:$G)/3,0)=AC2,YEAR(TimeSheet!$G:$G)=AC2,TimeSheet!$J:$J=C$2,TimeSheet!$B:$B=$B5,TimeSheet!$E:$E))
I have a formula to sum values "TimeSheet!E:E" according to month selected "AC2" and by ID & Emp, but what I have been struggling with is the other part of the formula if a month is not selected and a quarter is selected.
Instead of months as text, I use 1-12 to simplify formula. This is the formula that I am using.
=SUM((MONTH(TimeSheet!$G:$G)=$AC$2)*(TimeSheet!$B:$B=$B5)*(TimeSheet!$J:$J=E$2)**(TimeSheet!$E:$E))
AC2 (Month 1-12/1Qtr, 2Qtr, 3Qtr, 4Qtr) B:B (Emp Column) E:E (Miles Column) G:G (Dates Column) J:J (ID Column)
The formula I have been trying is:
SUM(SUMIF(AC2,{"1","2","3","4","5","6","7","8","9","10","11","12"},TimeSheet!$J:$J=C$2,TimeSheet!$B:$B=$B5,(MONTH(TimeSheet!$G:$G)=$AC$2,TimeSheet!$E:$E),SUMPRODUCT((ROUNDUP(MONTH(TimeSheet!$G:$G)/3,0)=AC2,YEAR(TimeSheet!$G:$G)=AC2,TimeSheet!$J:$J=C$2,TimeSheet!$B:$B=$B5,TimeSheet!$E:$E))