eomonth problem in sumproduct array formula

Question

I have a data table where I created a helper field [Month] from the [Production Day] field.  By doing that I get the first formula below to work properly.  I would like to not need the helper field [Month] and have the EOMONTH formula work in the OFFSET function against the [Production Day] field but I haven't had any luck.  I've tried multiple ways to use EOMONTH in and around the OFFSET formula for the [Production Day] without success.  Any ideas?

=SUMPRODUCT(--(OFFSET(DownTime[Month],0,0,,1)=EOMONTH(B$2,0)),--(OFFSET(DownTime[Machine],0,0,,1)=B$3),--(OFFSET(DownTime[Category],0,0,,1)=$A4),(OFFSET(DownTime[Minutes Down],0,0,,1)))

=SUMPRODUCT(--(OFFSET(DownTime[Production Day],0,0,,1)=EOMONTH(B$2,0)),--(OFFSET(DownTime[Machine],0,0,,1)=B$3),--(OFFSET(DownTime[Category],0,0,,1)=$A4),(OFFSET(DownTime[Minutes Down],0,0,,1)))

Answer 1

Great - happy to hear that you got it worked out: your initial post did not mention multi-year amounts of data, so I was just guessing, but can now see why you were using EOMONTH.

Answer 2

Bernie,

Your solution works as long as I add in the additional parameter for the year.  Here is what I ended up with.

=SUMPRODUCT(--(YEAR(DownTime[Production Day])=YEAR(B$2)),--(MONTH(DownTime[Production Day])=MONTH(B$2)),--(DownTime[Machine]=B$3),--(DownTime[Category]=$A4),(DownTime[Minutes Down]))

Thanks for the help.

Stuart

Answer 3

Did you try (I named my dates "Date" and not Production Day, but his corrects that)

=SUMPRODUCT((MONTH(Downtime[Production Day])=MONTH(B$2))*(Downtime[Machine]=B$3)*(Downtime[Category]=$A4)*Downtime[Minutes Down])

EOMONTH as you use it will return the last day of the month - the formula above checks that the month of the production date matches the month of the date in B2.

Answer 4

I updated my formula eliminating the offset which is cleaner, thanks for clarifying that.

=SUMPRODUCT(--(DownTime[Month]=EOMONTH(B$2,0)),--(DownTime[Machine]=B$3),--(DownTime[Category]=$A4),(DownTime[Minutes Down]))

However back to my original problem, I'd like to eliminate my helper field [Month] by letting an EOMONTH formula capture the records in the field [Production Day] where the production day falls in the current month identified by the EOMONTH(B$2,0) formula.  When I use the helper column in the formula above I get all the production days that fall in the month from B$2 (11/1/17 in this example), when I use the formula below it gives me the single record where [Production Day] is equal to EOMONTH(B$2,0).

=SUMPRODUCT(--(DownTime[Production Day]=EOMONTH(B$2,0)),--(DownTime[Machine]=B$3),--(DownTime[Category]=$A4),(DownTime[Minutes Down]))

When I use the formula below to attempt to get there I get #VALUE!, is there a way to get the EOMONTH result for each record in the [Production Day] field without the helper field [Month]?

=SUMPRODUCT(--(EOMONTH(DownTime[Production Day],0)=EOMONTH(B$2,0)),--(DownTime[Machine]=B$3),--(DownTime[Category]=$A4),(DownTime[Minutes Down]))

Thanks again for taking a look at this.

Answer 5

No need to use OFFSET rather than just the table column, (though I'm not sure what you want to do with the dates).

=SUMPRODUCT((MONTH(Downtime[Date])=MONTH(B$2))*(Downtime[Machine]=B$3)*(Downtime[Category]=$A4)*Downtime[Minutes Down])

You could also use an array formula like this, entered using Ctrl-Shift-Enter:

=SUM(IF(MONTH(Downtime[Production Day])=MONTH(B$2), IF(Downtime[Machine]=B$3,IF(Downtime[Category]=$A4,Downtime[Minutes Down]))))