Modify SumIf formula

willow1985

Active Member
I have this SumIf formula that Sums column G if the dates in Column F Match the previous day:

=SUMIF('WorkOrder Search Export'!G2:G1000000, TODAY()-1, 'WorkOrder Search Export'!F2:F1000000)

How would I modify this formula to do it for the current month and previous month

Date format in column F is: 2019-09-25

Thank you for your help in modifying this formula

Carla

RasGhul

Well-known Member
Hi Willow,

Adjust ranges as needed;

Yesterday
=SUMPRODUCT(('Workorder Search Export'!\$F\$2:\$F\$13=TODAY()-1)*'Workorder Search Export'!\$G\$2:\$G\$13)

Current Month
=SUMIFS('Workorder Search Export'!\$G\$2:\$G\$13,'Workorder Search Export'!\$F\$2:\$F\$13,">="&EOMONTH(TODAY(),-1)+1,'Workorder Search Export'!\$F\$2:\$F\$13,"<="&EOMONTH(TODAY(),0))

Previous Month
=SUMIFS('Workorder Search Export'!\$G\$2:\$G\$13,'Workorder Search Export'!\$F\$2:\$F\$13,">="&EOMONTH(TODAY(),-2)+1,'Workorder Search Export'!\$F\$2:\$F\$13,"<="&EOMONTH(TODAY(),-1))

Peter_SSs

MrExcel MVP, Moderator
Are you really using anything like a million rows? I would try to restrict that row range in your formulas if possible.

Apart from the above comment, I would stick with your current SUMIF rather than SUMPRODUCT for the 'yesterday' question, especially if you do have a very large data set.

For 'This Month', I think RasGhul has accidentally swapped the F/G columns & it can be simplified ever so slightly. Again adjust ranges to suit.
=SUMIFS('WorkOrder Search Export'!\$F\$2:\$F\$35,'WorkOrder Search Export'!\$G\$2:\$G\$35,">"&EOMONTH(TODAY(),-1),'WorkOrder Search Export'!\$G\$2:\$G\$35,"<="&EOMONTH(TODAY(),0))

'Last Month', similar comments
=SUMIFS('WorkOrder Search Export'!\$F\$2:\$F\$35,'WorkOrder Search Export'!\$G\$2:\$G\$35,">"&EOMONTH(TODAY(),-2),'WorkOrder Search Export'!\$G\$2:\$G\$35,"<="&EOMONTH(TODAY(),-1))

willow1985

Active Member
Thank you all very much!

Threads
1,085,317
Messages
5,382,935
Members
401,810
Latest member
ibusyed