For SUMPRODUCT / array formulas wizards : flatten 2D range in 1D array of information, for use in SUMPRODUCT

[fredericdenis]

Dear Excel gurus,

I feel this is possible, but I’m not familiar enough with Excel array formulas and pro uses of SUMPRODUCT function to find how to make it possible. I'm stuck and need your help.

Given a dynamic 2D range (result of an OFFSET function), I would like calculate a value for each column of that range, and pass that 1D array of values as an argument to the SUMPRODUCT function.

Specifically, each value of the 1D array would indicate if the corresponding column in the dynamic range had at least one non-empty cell (1) or if the whole column was empty (0).


Below is a table similar to what I’m working with. Idea is we have Items (standalone or part of a group) that will be packed in box(es). If a line is a group, the number of different items in a given group is indicated in column B (and the line of these items will always be following the group line). Range D4:G8 serves to enter quantity of item in each box. No quantity is entered for a group.

For each group, however, I want to calculate the total weight of all boxes containing items from that group. So, for each item that is part of that group (line), check in each box (column) if there is a quantity present (1) or not (0), and multiply that by the weight of the box.

In other words, I think I need a function like:

I4 = SUMPRODUCT( D2:G2; ColumnsAreNotEmpty( OFFSET(D4:G4;1;0;B4 )) )

…where ColumnsAreNotEmpty() would return a 1D array with the values {1,1,1,0} in this particular example. However, is it possible to achieve this without writing a function? Can this be done just with SUMPRODUCT and/or the use of an array formula?

Note that, for usability purposes, I would like the table of quantities (D4:G8) to contain only user-entered data, no formula. It would be to easy otherwise… :P


Thanking you in advance for any hint, advice or answer,


FD


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]Box ID[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]Weight[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GRP/ITEM[/TD]
[TD](nbItems)[/TD]
[TD]Total Qty[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Total weight of boxes with items in group?[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]GRP1[/TD]
[TD="align: right"]2[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: center"]75 (= weight of boxes 1+2+3)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]ITEM1[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]ITEM2[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]ITEM3[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]ITEM4[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Hi
Welcome to the board.

For your exampe, try:

=SUMPRODUCT( D2:G2,0+(SUBTOTAL(2,OFFSET(D4:G4,1,COLUMN(D4:G4)-MIN(COLUMN(D4:G4)),B4,1 ))>0))

 

[fredericdenis]

Wow.
I knew it would be possible, but I don't know how many hours of reading I would have needed to come up with this.

I understand that the 0+ part is to convert the x>0 to a numerical value of 0 or 1 (instead of boolean TRUE or FALSE).
I understand the SUBTOTAL(2,...) will count the number of values in each column.
I understand the OFFSET, generally speaking.

What puzzles me is the column parameter of the OFFSET : COLUMN(D4:G4)-MIN(COLUMN(D4:G4)).
I understand this is the trick to force Excel to cycle each cell in the array during the calculation. And if I'm right, then in each iteration, it will return 0.

Otherwise, to have tried it, I know that:
OFFSET(D4:G4,1,0,B4,1)
...doesn't work. Returns #VALUE error.
and:
OFFSET(D4:G4,1,COLUMN(D4:G4),B4,1)
...doesn't work either. Returns 0.

But how internally each one is calculated, I don't understand.
(Being on Mac, I don't have the "evaluate formula" feature but I plan on spending some time on a PC to understand each option).

If you or someone you know already wrote something explaining how SUMPRODUCT works under the hood in this regard, I'd be glad to read.


But beyond all that, I wanted to thank you very, very much for your help, pgc01. You saved me probably 2 to 3 days of trial-and-error.
:D

 

[pgc01]

Hi
I'm glad it helped.

You cannot use directly SUBTOTAL(2,OFFSET(D4:G4;1;0;B4)) because that would calculate the SubTotal() for the whole range, which is not what you want.

What you want is to calculate the SubTotal() for each one of the 4 columns separately.

I used COLUMN(D4:G4)-MIN(COLUMN(D4:G4)) as the column offset parameter (and 1 as the column count, the 5th parameter)

COLUMN(D4:G4)-MIN(COLUMN(D4:G4)) is equal to the array {0,1,2,3} and so I have

OFFSET(D4:G4,1,{0,1,2,3},B4,1)

This separates the 4 columns, it's just as if I had an array with the 4 columns, like (remember B4 = 2)

OFFSET(D4:G4,1,0,2,1) OFFSET(D4:G4,1,1,2,1) OFFSET(D4:G4,1,2,2,1) OFFSET(D4:G4,1,3,2,1)

which is

D4:D5 E4:E5 F4:F5 G4:G5

and so the complete SubTotal

SUBTOTAL(2,OFFSET(D4:G4,1,COLUMN(D4:G4)-MIN(COLUMN(D4:G4)),B4,1))

counts the numbers is each of the 4 columns of the range {2,1,2,1}

Does this clarify?

 

[pgc01]

Sorry, the last result is wrong

....

counts the numbers is each of the 4 columns of the range {2,1,2,1}

Does this clarify?

Should be:

{1,2,1,0}

 

[fredericdenis]

It does clarify. Thanks a million. Send me your invoice!

 

[pgc01]

You're welcome.