Modify SumIf formula

willow1985

Active Member
Joined
Jul 24, 2019
Messages
422
Office Version
2019
Platform
Windows
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
Joined
Jul 15, 2016
Messages
608
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
Joined
May 28, 2005
Messages
42,627
Office Version
365
Platform
Windows
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))
 

Forum statistics

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

Some videos you may like

This Week's Hot Topics

Top