How to skip cells when creating an array in excel?

I want to cells to be excluded from an array formula when I create the array, as in not have the array in those cells. Is this possible?

In this image, the left table is the data that I'm using to produce the reports in the center and on the right. In the actual spreadsheet the data is spread out across multiple sheets, and more or those sheets are added monthly.

Currently the center table is what using an array for output produces, this causes the sums to be duplicated on the rows where the task number is for the subitems. I cannot (am not allowed to) remove the task number for these subitems.

The table on the right is what I have currently, with a formula in each of the filled amount boxes. However, since this formula in reality consists of multiple sumifs for the different data sheets, I'd like to have to only update one cell and CTRL+SHIFT+ENTER to update the whole column of cells. This table is also what cells I'd like for the array to be in.

A formula from the actual sheet:

=SUMIFS('April 18'!S:S,'April 18'!M:M,Summary!A:A,'April 18'!O:O,"4.2018")+SUMIFS('May 18'!T:T,'May 18'!N:N,Summary!A:A,'May 18'!P:P,"4.2018")+SUMIFS('July 18'!$U:$U,'July 18'!$N:$N,Summary!$A:$A,'July 18'!$Q:$Q,"4.2018")+SUMIFS('June 18'!$U:$U,'June 18'!$N:$N,Summary!$A:$A,'June 18'!$Q:$Q,"4.2018")

Solution 1:

Lots of little things to assume here, but as they say, the devil is in the details.

My assumptions will be that the Task Numbers for subitems are identical to the non-subitem that will lead them in the table. As an example, cell K3 contains a non-subitem since it is the first of one or more that have a particular Task Number. K4, K5, and K6 have subitems because their Task Numbers are the same as K2's Task Number.

If this is so, you can use the following formula in column L's cells:

=IF(J2=J1,"",SUMIFS($C:$C,$A:$A,$F$3:$F:$13,$B:$B,"7.2018")

For the successful IF() tests, if you need 0's instead of "", just change that part. An example of when issues can arise would be some ways of adding the values in this column (column L). If you'd like the cells blank, yet having a 0 for that kind of concern, just format the column so that the third formatting parameter (for when a cell equals 0) as nothing. For example: 0,000.00;-0,000.00; and cells with a value of 0 will display as blank.

The rest of the formula is the formula the picture shows.

If this is not a correct assumption about non-subitems and subitems, all is not lost. You surely have some way we do not see of identifying subitems as such. And presumably non-subitems as such. Just use the same approach above, but use that way you have for identifying the difference between items. I used the method I did since it looks like it is valid and let the solution be complete in what is presented. But the cleanest way is using whatever way you have to identify the difference.

I point out that you must surely have one because of the indentation of subitems. That has to happen due to some known difference. It is not something that Excel would bring along with a value found somewhere. It WILL do that with numbers if a cell with default formatting gets a formula referring to some formatted cell. But not strings. So there must be some way. Just check for that difference in the IF() test.