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....

Sans VBA?

In C1, enter

=IF(A2="",SUM(INDEX(B:B,MAX(1,MAX(IF($A$1:A1="",RO W($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)

"ConfusedNHouston" wrote in
message ...
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....

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" wrote:

Sans VBA?

In C1, enter

=IF(A2="",SUM(INDEX(B:B,MAX(1,MAX(IF($A$1:A1="",RO W($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)

"ConfusedNHouston" wrote in
message ...
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....

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)

"ConfusedNHouston" wrote in
message ...
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" wrote:

Sans VBA?

In C1, enter

=IF(A2="",SUM(INDEX(B:B,MAX(1,MAX(IF($A$1:A1="",RO W($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)

"ConfusedNHouston" wrote in
message ...
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....

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).Are as
myArea.Cells(myArea.Cells.Count + 1).Formula = "=SUM(" & myArea.Address & ")"
Next myArea
End Sub

HTH,
Bernie
MS Excel MVP


"ConfusedNHouston" wrote in message
...
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....