Having trouble with array formulas

[vrenaut74]

There is an existing excel Maintenance program that is no longer working on my computer, I must have done something to the formulas by accident and saved it.

The Maintenance program uses some type of Array formula that use dates to from one sheet, to tell me when certain procedures are due on another sheet. These dates are determined by a pair of dates that I input into cells H7 and I7 on the "weekly" sheet.

Below are screen shots and the formulas for the two cells that I'm having a problem with.

Cell H12 --> =SUMPRODUCT((Master!F6:F188>=Weekly!H7<=I7),((Master!C6:C188="Annual")+(Master!C6:C188="Semi-")+(Master!C6:C188="Quarterly")+(Master!C6:C188="Monthly")))

Cell A26 --> =IF(ROWS(A$26:A26)>Weekly!$H$12,"",INDEX(Master!A$6:A188,SMALL(IF((Master!$F$6:$F188>=Weekly!$H$7)*(Master!$F$6:$F188<=Weekly!$I$7)*((Master!$C$6:$C188="Annual")+(Master!$C$6:$C188="Semi-")+(Master!$C$6:$C188="Quarterly")+(Master!$C$6:$C188="Monthly")),ROW(Master!A$6:A188)-ROW(Master!A$6)+1),ROWS(A$26:A26))))

Cell A26 formula is then expanded by dragging the bottom right corner to the other cells that have the #VALUE! in them as well.

Here is the excel sheet:

Master Maintenance.xlsx

[canapone]

Hi,

an attempt, I cannot see your attachments: probably you did not confirm correctly array formula.

In A26, press F2 and confirm the formula with control+shift+enter.

Again: just an attempt

[vrenaut74]

Hi,

an attempt, I cannot see your attachments: probably you did not confirm correctly array formula.

In A26, press F2 and confirm the formula with control+shift+enter.

Again: just an attempt

Sorry about that, I'll try to get that fixed.

I have tried CTRL + Shift + ENT with no luck.

[ben_hensel]

What are you trying to do with the first term of the SUMPRODUCT?

Please Login or Register  to view this content.

That will return FALSE basically no matter what, so what is it supposed to be doing instead?



Anyway, please post your spreadsheet instead of just a screencap. That will be much easier to troubleshoot.

[vrenaut74]

What are you trying to do with the first term of the SUMPRODUCT?

Please Login or Register  to view this content.

That will return FALSE basically no matter what, so what is it supposed to be doing instead?



Anyway, please post your spreadsheet instead of just a screencap. That will be much easier to troubleshoot.

Someone else wrote the formula, they are no longer here, I am just trying to troubleshoot it.

That piece of formula, I'm guessing, is telling excel to only mention the "weekly" maintenance that is in between the dates entered into cells H7 and I7.

I am trying to to attach my excel spreadsheet but it is not letting me, I'll try to get it to work.

thanks

[Speshul]

Master!F6:F188>=Weekly!H7<=I7

That is saying this:

F6:F188>=H7

Which will return something like (based on the values)

{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE....}

The second part is doing this

{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE....}<=I7

So if I7 is any number LARGER than 0 it will do this
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE...}



...i think

[vrenaut74]

What are you trying to do with the first term of the SUMPRODUCT?

Please Login or Register  to view this content.

That will return FALSE basically no matter what, so what is it supposed to be doing instead?



Anyway, please post your spreadsheet instead of just a screencap. That will be much easier to troubleshoot.

Here is the excel sheet.

Master Maintenance.xlsx

[vrenaut74]

I figured out how to attach my excel sheet, it is in the original post of this thread.

Could someone see if they can troubleshoot it. Been trying everything but can't figure it out.

Thanks

[daddylonglegs]

That piece of formula, I'm guessing, is telling excel to only mention the "weekly" maintenance that is in between the dates entered into cells H7 and I7

That sort of "between" syntax doesn't work, you have to use two separate checks like (range>111)*(range<999) so in your formula that will be like this:

=SUMPRODUCT((Master!F6:F188>=H7)*(Master!F6:F188<=I7),(Master!C6:C188="Annual")+(Master!C6:C188="Semi-")+(Master!C6:C188="Quarterly")+(Master!C6:C188="Monthly"))

For your data, though, that still gives me #VALUE! error because you have some #VALUE! errors in the Master!F6:F188 range - either eliminate those errors so that the above formula works or try using COUNTIFS like this:

=SUM(COUNTIFS(Master!F6:F188,">="&H7,Master!F6:F188,"<="&I7,Master!C6:C188,{"Annual","Semi-","Quarterly","Monthly"}))

[vrenaut74]

That sort of "between" syntax doesn't work, you have to use two separate checks like (range>111)*(range<999) so in your formula that will be like this:

=SUMPRODUCT((Master!F6:F188>=H7)*(Master!F6:F188<=I7),(Master!C6:C188="Annual")+(Master!C6:C188="Semi-")+(Master!C6:C188="Quarterly")+(Master!C6:C188="Monthly"))

For your data, though, that still gives me #VALUE! error because you have some #VALUE! errors in the Master!F6:F188 range - either eliminate those errors so that the above formula works or try using COUNTIFS like this:

=SUM(COUNTIFS(Master!F6:F188,">="&H7,Master!F6:F188,"<="&I7,Master!C6:C188,{"Annual","Semi-","Quarterly","Monthly"}))

thank you!

I will give this a try.