SUM ROWS and RETURN DATA AS ARRAY

[PaulGotts]

Hello there,

I have used MMULT to create an array of values visible in the formula bar when I hit F9.

I want to sum only the rows within these values and be able to see said sum (separated by semi-colons) in the formula bar when I hit F9.

I want to be able to continue to apply further functions to these row summations.

Because there are 3400 rows and possibly 100 columns, it is not feasible (would take too long) to run intermediary steps in 340,000 cells.

If you have a suggestion as to how to attain this I am all ears.

Thanks kindly,
Paul.

 

[barry houdini]

Hello Paul,

Can you post the formula you are using to create the array?

 

[PaulGotts]

Hello Paul,

Can you post the formula you are using to create the array?

Sorry for the delay. I ended up being able to solve it using this formula below. MMULT using a column of 1's provided me with what I needed (summed each row and returned an array for further functionality in the same formula).

{=IFERROR(SUM(--(((MMULT(--(MOD(COLUMN(INDEX('Ship vs Scan'!$C$7:$DZV$7,,MATCH($E4,'Ship vs Scan'!$C$7:$DZV$7,0)):OFFSET(INDEX('Ship vs Scan'!$C$7:$DZV$7,,MATCH($E4,'Ship vs Scan'!$C$7:$DZV$7,0)),,MATCH(J$3,'Ship vs Scan'!$C$8:$DZV$8,0)))-COLUMN(INDEX('Ship vs Scan'!$C$7:$DZV$7,,MATCH($E4,'Ship vs Scan'!$C$7:$DZV$7,0)))+0,2)=0)*OFFSET(INDEX('Ship vs Scan'!$C$7:$DZV$7,,MATCH($E4,'Ship vs Scan'!$C$7:$DZV$7,0)),3,,3400,MATCH(J$3,'Ship vs Scan'!$C$8:$DZV$8,0)+1),OFFSET($BN$4,,,MATCH(J$3,'Ship vs Scan'!$C$8:$DZV$8,0)+1)))*(--(('Ship vs Scan'!$B$10:$B$3409)>0)))>0)),"")}

 

[PaulGotts]

Sorry, a little explanation of this formula would probably be helpful...

I had a set of data which was organized similar to this...

		Product1	Product2
		Week1	Week2	Week3	Week1	Week2	Week3
		UnitShip	UnitSold	UnitShip	UnitSold	UnitShip	UnitSold	UnitShip	UnitSold	UnitShip	UnitSold	UnitShip	UnitSold
Region1	Store1	0	0	0	0	3	0	3	2	3	4	3	5
	Store2	0	0	0	0	1	0	0	0	0	0	0	0
	Store3	0	0	0	0	3	0	3	2	3	4	3	5
	Store4	0	0	0	0	1	0	0	0	0	0	0	0
Region2	Store1	0	0	0	0	3	0	3	2	3	4	3	5
	Store2	0	0	0	0	1	0	0	0	0	0	0	0
	Store3	0	0	0	0	3	0	3	2	3	4	3	5
	Store4	0	0	0	0	1	0	0	0	0	0	0	0

<COLGROUP><COL style="WIDTH: 48pt" span=14 width=64><TBODY>
</TBODY>

ETC.
ETC.
ETC.
|
|
|
V

The formula in the previous post allows me to count the # of Stores (not units) which have shipped (or, with minor edits, scanned) the product at any week up to and including the current week (or week of choice). I wanted to figure out how many stores were shipping the product and compare it to how many stores were scanning the product. This formula works with 3400 stores (rows) worth of data and up to 100 products, each with ~28 weeks of data (column DZV7).
This formula enables the user to avoid "processor hogging" intermediate steps (~500,000 of them) by listing the # of stores in 1 cell/week/product (<=28 formulas per product... much faster).
Basically, the formula is written in such a way that it can be copied right and down and not need changing other than to replace the "+0" with "+1" if Scanned stores are needed [i.e. skipping every other column but starting on the UnitScan column (+1) rather than the UnitShip column (+0)].

Maybe this will help others who have similarly complex problems they need to solve without VBA.

Sorry for any confusion! My best advice would be to study Mr.Excel and Excelisfun YouTube videos for each function you don't know.
Thanks,
Paul.

 

[PaulGotts]

Properly Formatted Table:

		Product1						Product2
		Week1		Week2		Week3		Week1		Week2		Week3
		UnitShip	UnitSold	UnitShip	UnitSold	UnitShip	UnitSold	UnitShip	UnitSold	UnitShip	UnitSold	UnitShip	UnitSold
Region1	Store1	0	0	0	0	3	0	3	2	3	4	3	5
	Store2	0	0	0	0	1	0	0	0	0	0	0	0
	Store3	0	0	0	0	3	0	3	2	3	4	3	5
	Store4	0	0	0	0	1	0	0	0	0	0	0	0
Region2	Store1	0	0	0	0	3	0	3	2	3	4	3	5
	Store2	0	0	0	0	1	0	0	0	0	0	0	0
	Store3	0	0	0	0	3	0	3	2	3	4	3	5
	Store4	0	0	0	0	1	0	0	0	0	0	0	0

<COLGROUP><COL style="WIDTH: 48pt" span=14 width=64><TBODY>
</TBODY>