INDIRECT and SUMIF with Arrays

[CDelSignore]

Here's my formula: =IF((INDIRECT($C$6)+1/24)>=0,IF((INDIRECT($C$6)+1/24)<(8/24),SUMIF(INDIRECT(B$6),B10,INDIRECT(D$6)),0),0)

C6: 'Sheet1'!$C$3:$C$10 (Sheet1 is data entry and column C is time, in 24-hour format)
B6: 'Sheet1'!$B$3:$B$10 (Sheet1 is data entry and column B is the Date)
D6: 'Sheet1'!$H$3:$H$10 (Sheet1 is data entry and column H is the first possible location for a nonconformance (17 possible locations total)​

On this Worksheet, B1 is a SUMPRODUCT formula to find the last row used on the Data Entry Worksheet, B3 (Row 3, starting at column B) is the name of the worksheet the data is being pulled from. B4 (Row 4, starting at Column B) defines the initial Cell for a Range. B5 (Row 5, starting at Column B) defines the final cell for a range, using the value in B1 as the Row value). B6 puts B3 through B5 together in the correct syntax to be used by an Indirect function. The contents of those cells and values are as follows:

B1: =SUMPRODUCT(MAX((Sheet1!$A:$A<>"")*ROW(Sheet1!$A:$A))); Value=10
B3: Sheet1; Value=Sheet1
B4: $B$3; Value = $B$3
B5: ="$B$"&$B$1; Value= $B$10
B6: ="'"&B3&"'!"&B4&":"&B5; Value='Sheet1'!$B$3:$B$10​

I couldn't find any other way to automatically update whenever new data was entered. On another section of this worksheet (Named according to the Month), I'm compiling all the data according to date. There could be any number of entries for each date (usually 75-125 entries). Granted, there are only 8 entries so far, and all from the same month, but it seems to be working just fine.

The function used to compile according to date (the indirect reference cells are the same format as B6 above):

=SUMIF(INDIRECT($AN$6),$AN10,INDIRECT(AO$6))​

AN6 is the date range on Sheet1. AN10 is the date on this worksheet. AO6 is the nonconformance location on Sheet1.

Like I said, this seems to be working out just fine, and auto updates as new data is entered. I'm using the same data, but trying to break it down into which shift the widget was made on that has the nonconformance. What I'm doing is looking at the date range (defined in B6), taking the time range ( defined in C6), adding 1/24 (1 hour) and comparing it to >=0 or <0.3333 (3rd shift is 11p-7a, so adding an hour to the time and comparing it to 0:00-8:00 should identify 3rd shift parts) and summing the cells in the range (defined in D6). Stepping through the formula looks like it's working properly right up until the end and I get a zero (the data has been entered so that I should get a value of 2). Here's my 3rd shift formula:

​

=IF((INDIRECT($C$6)+1/24)>=0,IF((INDIRECT($C$6)+1/24)<(8/24),SUMIF(INDIRECT(B$6),B10,INDIRECT(D$6)),0),0)
​

The range of times +1/24 is initially checked to see if it's equal to or greater than 0, and I get all trues (which is correct for all 8 entries). The True part of the IF statement then checks to see if the range of times +1/24 is less than 8/24 (0.3333), and I get {False,True,False,True,True,True,True,True} (which, again, is correct for all 8 entries). The True part of the second IF statement then Sums the values in the range defined by D6 if the date ranges defined in B6 are equal to the date in B10 of the current worksheet, and the False portions of both If statements are 0. The True value of the second IF statement even correctly evaluates as 2, but when I Ctrl+Shift+Enter, the cell value is 0.

What am I missing?

 

[RoryA]

Your formula returns an array of results, not one number. Since the first item in the array returns 0, that's what you see in the cell.

 

[CDelSignore]

Your formula returns an array of results, not one number. Since the first item in the array returns 0, that's what you see in the cell.

How do I get it to show just the one number? Do I have to change from IF statements to SUMIFS statements?

 

[RoryA]

It appears so. You can probably use:

=SUMIFS(INDIRECT(D$6),INDIRECT($C$6),">=-1/24",INDIRECT($C$6),"<7/24",INDIRECT(B$6),B10)

 

[CDelSignore]

It appears so. You can probably use:

=SUMIFS(INDIRECT(D$6),INDIRECT($C$6),">=-1/24",INDIRECT($C$6),"<7/24",INDIRECT(B$6),B10)

I still get a result of 0, when the correct result is 2.

On another note, when you use "-1/24," does that mean something that happened after 23:00 on 6/1/2017 will be treated as occurring on 3rd shift on 6/2/2017?

 

[RoryA]

I used it simply because you used it in your formula by adding 1/24 and testing for >=0. I don't know what is where in your worksheet. If that column contains date times, then the test makes no sense.