Solved Can I use COUNTIFS and EOMONTH together?

Microsoft Excel 2016 suites
June 3, 2019 at 15:13:47
Specs: Windows 10
I'm trying to analyze work orders and would like to be able to count how many work orders were closed last month in one cell, and all work orders that are closed in the table in a different cell. I'm already able to look at all work orders in the table that have been closed with:

=COUNTIFS('Work Orders'!C:C,$G$3,'Work Orders'!E:E,B8)

But I can't seem to get it to just look at what was closed during the previous month. I've tried:

=COUNTIFS('Work Orders'!C:C,$G$3,'Work Orders'!E:E,B8, 'Work Orders'!L:L, AND(>=EOMONTH('Work Orders'!L:L,-2)+1), <=EOMONTH('Work Orders'!L:L,-1))

Obviously this didn't work, but I've never used EOMONTH or tried to do anything like like this before, so I'm not even sure where to go from here.

On the "Work Orders" tab:
Column C is the name of the property
Column E is the Job Type
Column L is the closed date.

On the current tab:
G3 is the name of the property (using a dropdown)
B8 is the job type

Is what I'm trying to do even possible?

Thank you!

message edited by tgordon21


See More: Can I use COUNTIFS and EOMONTH together?

Reply ↓  Report •

#1
June 3, 2019 at 19:46:13
✔ Best Answer
Try this:

=SUMPRODUCT(--('Work Orders'!C2:C200=$G$3),--('Work Orders'!E2:E200=$B$8),--(MONTH('Work Orders'!L2:L200)=MONTH(EOMONTH(TODAY(),-1))))

NOTE: You have to use a defined range, ie: C2:C200 if your columns have a header row, else you get an error.
It uses the MONTH() function to get last month's number.

It's not a COUNTIFS & EOMONTH but seems to work.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
June 4, 2019 at 06:34:58
Further to Mike's excellent suggestion, if you don't want to use a defined range, you can use a defined name. Caveat: This assumes that there are no blank cells within the data in the referenced columns.

If you create 3 Dynamic Named ranges, e.g. ColC, ColE and ColL, you can use this formula:

=SUMPRODUCT(--(ColC=$G$3),--(ColE=$B$8),--(MONTH(ColL)=MONTH(EOMONTH(TODAY(),-1))))

An example of a Dynamic Named Range in this case would be to use this in the Refers To field of the Define Name wizard.

For the ColC name:

='Work Orders'!$C$2:INDEX('Work Orders'!$C:$C,COUNTA('Work Orders'!$C:$C))

As you add values to bottom of the columns, the length of the defined range will increase.

message edited by DerbyDad03


Reply ↓  Report •

#3
June 4, 2019 at 10:14:33
This is great! Thank you! Honestly, I've never used SUMPRODUCT before, so I had no idea it could work like this.

I just have one follow up question. I was able to adapt this formula to work for the other metrics the spreadsheet looks at, but I'm not sure how to get it to work for the average amount of days a job type takes to be completed. Like before, I can calculate it for the entire table, just not for the previous month.

The formula for the entire table is:

=IFERROR(AVERAGEIFS('Work Orders'!M:M,'Work Orders'!C:C,$K$3,'Work Orders'!E:E,B6),0)

Where column M on the Work Orders sheet is the amount of days each work order took to complete, and $K$3 is the new home for the property name on the current sheet. All the other columns and cells are the same as before.

You're formula works great! I needed to do some tweaking to make it work for the spreadsheet, but it's pretty much the same. What I have is:

=SUMPRODUCT(--('Work Orders'!$C$8:$C$2000=$K$3),--('Work Orders'!$E$8:$E$2000=B6),--(MONTH('Work Orders'!$L$8:$L$2000)=MONTH(EOMONTH(TODAY(),-1))))

Is there a way to get an average from these?

Thank you!

message edited by tgordon21


Reply ↓  Report •

Related Solutions

#4
June 4, 2019 at 19:31:32
The easiest way is to add a Helper column that has the Completed Month number, then you can do an AVERAGEIFS()

So, someplace on your Work Orders sheet, like column AA enter the formula:

=MONTH(L2)

Drag down as many rows as needed.
You should now have the Month Number in column AA

Next, on the summary sheet you'll want to add a cell that contains the month number your interesed in, I have used cell B10, but it can be any cell you like.

Now we can use a AVERAGEIFS() to get the Month & Job Type:

=AVERAGEIFS('Work Orders'!M2:M27,'Work Orders'!E2:E27,B8,'Work Orders'!AA2:AA27,B10)

Column M has the hours
Column E is Job Type
Column AA is the Month Number

Also since we now have the Month Numbers you can use a COUNTIFS() in place of the SUMPRODUCT()

=COUNTIFS('Work Orders'!C2:C27,$G$3,'Work Orders'!E2:E27,B8,'Work Orders'!AA2:AA27,MONTH(EOMONTH(TODAY(),-1)))

See how that works for you.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#5
June 5, 2019 at 11:12:20
Hi Mike, thanks for the follow up! The only problem is this method would return ALL work orders that were closed in a particular month, regardless of which year that was. (March 2018 would be included with March 2019) I need it to be able to look at just the most recent previous month.

What I'm trying to do is build this so that I can just download the data from our work order system for all our closed work orders, drop it into the Work Orders tab, and the summary tab would automatically be updated. It has sections to display data for the entire database as well as sections to display just the previous month (and it does need to be the previous month, not just the last 30 days).

Would it be easier if I sent you a copy of the spreadsheet so you can see what I mean? It would be too big to put on here and have it make sense.

Thank you!

message edited by tgordon21


Reply ↓  Report •

#6
June 5, 2019 at 11:35:56
Not tested:

Just check for the current year with an IF...

=IF(YEAR(L2) = YEAR(TODAY()),MONTH(L2),"")

You may need to modify that to deal with January looking back into last year's December, but I'm sure it could be done.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#7
June 5, 2019 at 12:26:21
OK, my head hurts, but I think this gets you the months you need. It returns the previous "12" when the current month is January and it returns the months for the current year when it's not January. I think. ;-)

=IF(AND(MONTH(TODAY())=1,MONTH(L2)=12,YEAR(L2)+1=YEAR(TODAY())),MONTH(L2),
IF(AND(MONTH(TODAY())<>1,YEAR(TODAY())=YEAR(L2)),MONTH(L2),""))


The only way to test this is to change the date on your computer and then press F9 so that the sheet recalculates the TODAY() function.

Just be careful when you change the system's date because other programs might be using it. I have one application that constantly monitors/displays real-time data from an external source. When I change my system date, that application complains and eventually shuts down due to an "illegal query" because the date on my side doesn't match the date on the external source.

message edited by DerbyDad03


Reply ↓  Report •

#8
June 5, 2019 at 12:49:04
Thank you! I'll put that in on the Work Orders tab so I don't run into issues next January.

Your last post actually gave me an idea that I've been working on, but it's not working out.

I have another tab, "Data Validation", where I keep my drop down lists. On this tab I put =TODAY() in cell E3, then =EOMONTH(E3,-2)+1 in cell E4, then formatted E4 to just display the month number.

Then I went to the "Work Orders" sheet, and included a column (column O) for Month and entered =IF(YEAR(L8)=YEAR(TODAY()),MONTH(L8),"") for each work order so it would return the month the work order was closed in if it's in the current year.

Then on the Summary (actually called "Analysis") sheet I entered =AVERAGEIFS('Work Orders'!M:M,'Work Orders'!E:E,B6,'Work Orders'!O:O,'Data Validation'!$E$4)

But this just gives me the #DIV/0! error. I'm not sure why, it looks like it should work. But I can't figure it out.

message edited by tgordon21


Reply ↓  Report •

#9
June 5, 2019 at 13:07:48
That last formula would go where you put Mike's =MONTH(L2) formula. All that is is an expansion of his idea to deal with your comment "The only problem is this method would return ALL work orders that were closed in a particular month, regardless of which year that was.

In Response #6 I "solved" that issue by checking the year. Then I realized that that would cause a problem with January since the current year wouldn't match the year of the previous month (December). Then I had to make sure that it didn't pull both the previous year's December and the current year's December.

There may be a way to shorten it (Mike?) but I went around in too many circles so I posted something that seems to work.

As far as the formula that returns the #DIV/0! error, I'll admit that I haven't been following everything that closely, just playing with formulas as they were posted. I'd have to dig deeper to see what's going on there, but can't do that right now.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#10
June 5, 2019 at 14:39:29
OK, quick fix for the Year problem:

Change the formula in column AA to:

=MONTH(L2)&" "&YEAR(L2)

That should give you the Month & Year with a single space separating them ie: 5 2019

Next you have Two Options:

First Option:
On the summary sheet add a cell that contains the Year number your interested in,
I have used cell B11, but it can be any cell you like.

Now change the AVERAGEIFS() formula to include the Year cell like:

=AVERAGEIFS('Work Orders'!M2:M27,'Work Orders'!E2:E27,B8,'Work Orders'!AA2:AA27,B10&" "&B11)
Note that I added a Space between the two cells

Second Option:

Do not chnage the formula, simply us the B10, but then you HAVE to enter the data as Month <space> Year, else you get an error.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#11
June 6, 2019 at 06:48:19
Mike: Will your suggestion work next January when it's time to look back into December?

(I don't have a workbook set up to test anything)


message edited by DerbyDad03


Reply ↓  Report •

#12
June 6, 2019 at 08:09:13
It should, because your inputting the Month & Year manually,
so you can look at any month & year combination you wish.
You are not limited to doing a look-back of only one month.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#13
June 6, 2019 at 12:53:17
Thanks Mike! I've changed the formula on the Work Orders tab to the =MONTH(L2)&" "&YEAR(L2) and it's working great! That was a really smart solution.

I've set it up basically the way you describe in your second option. See message #8 above. I changed the formatting to display the month and year though (5 2019) so it's the same as on the Work Orders tab. I'm still getting the #DIV/0! error though. I've tried moving it so it's on the same tab, but it's still the same.

Any idea why I'm getting the error?


Reply ↓  Report •

#14
June 6, 2019 at 17:55:59
Which formula is giving you the #DIV/0 error?

A #DIV/0 error usually occurs when you are dividing by 0 or a Blank Cell.
It may also be that the formula is waiting for input from another formula
or someone to enter the data.

Also not sure what you mean by:
I changed the formatting to display the month and year though (5 2019) so it's the same as on the Work Orders tab.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#15
June 7, 2019 at 08:01:23
I think your getting confused with Formatting cells.

I changed the formatting to display the month and year though (5 2019) so it's the same as on the Work Orders tab.

Changing the Format of a cell does not alter the underlying data.
So formatting a Date to display only the Month & Year does not change the
underlying date, it's still a full date.

On this tab I put =TODAY() in cell E3, then =EOMONTH(E3,-2)+1 in cell E4, then formatted E4 to just display the month number.

Cell E4 will still have a full date, ie 05/01/2019
but will DISPLAY 05

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#16
June 7, 2019 at 08:22:52
Thank you for the clarification. I guess I assumed the formula would just look at whatever data was displayed, but I'll make sure to remember it looks at the underlying data.

I'm getting the #DIV/0! error on the summary tab with this formula:

=AVERAGEIFS('Work Orders'!M:M,'Work Orders'!E:E,B6,'Work Orders'!O:O,'Data Validation'!$E$4)

On the Work Orders tab, column M is the amount of days each work order took to complete, column E is the job type, and column O is where I put your =MONTH(L8)&" "&YEAR(L8) formula to pull the month and year from the Closed Date. Cell B6 on the summary tab is the job type that I'm trying to average. Cell E4 on the Data Validation tab is the first day of the previous month (=EOMONTH(E3,-2)+1).

I've gone over it several times, but I'm not sure what the problem is. It looks like it should work. Any idea where the problem is?


Reply ↓  Report •

#17
June 7, 2019 at 08:47:15
=EOMONTH(E3,-2)+1

E3 is TODAY() so than means E4 will return a FULL date

column O is where I put your =MONTH(L8)&" "&YEAR(L8)

Column O has Month & Year

So formula ,'Work Orders'!O:O,'Data Validation'!$E$4

Will FAIL

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#18
June 7, 2019 at 09:04:21
That's great, thank you! I was able to use the same formula as on the Work Orders tab to pull just the month and year from E4 on the Data Validation tab, and it fixed the error! I changed it to =MONTH(E4)&" "&YEAR(E4).

I'll make sure to remember that Excel looks at the data in a cell, not just what is displayed.

Thanks again!


Reply ↓  Report •

Ask Question