Macro to Sum Arrays of Various Size

[Guest]

I have about 1500 sets of data in a single column that I need to sum,
individually.

Example

Product A $500
Product A $200
Product A $150

Product B $200
Product B $100

I can write a macro that searches for the empty cell (below $150 for Product
A and below $100 for product B), and I could then use the sum function; but
in one case I'm summing 3 terms, in the second case, I'm summing 2. I don't
know how to "tell the macro" (I'm assuming it's a relative reference macro),
how to determine the number of terms to sum.

Thanks, as always....

 

[Bob Phillips]

Sans VBA?

In C1, enter

=IF(A2="",SUM(INDEX(B:B,MAX(1,MAX(IF($A$1:A1="",ROW($A$1:A1))))):A1),"")

and copy down.

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bernie Deitrick]

Houston,

Take out the spaces and use a pivot table.

Or use a macro like this, to enter the sums in column B:

Sub EnterSums()
Dim myArea As Range

For Each myArea In Range("B:B").SpecialCells(xlCellTypeConstants).Areas
myArea.Cells(myArea.Cells.Count + 1).Formula = "=SUM(" & myArea.Address & ")"
Next myArea
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

i don't understand the part about "committing" with the Ctrl + Shft + Enter.
I copied the argument into C1 and dragged it down through two sets of data.
At the end of the first set, it gave me an accurate sum of the values
associated with the independent variable (product name). At the end of the
second set of product names, it returned a running sum; the sum of the first
set of products plus the sum for the second set of products - basically a
running grand total for the spreadsheet.

I've checked the syntax and I wrote the statement exactly as you did above.

Was this statement supposed to go into a macro? I just wrote it and pasted
it into column C.

What am I missing? Thanks....

 

[Bob Phillips]

Enter the formula in C 1, and instead of hitting Enter to finish the
formula, use Ctrl-Shift-Enter all together. Then drag-copy it down.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)