Sum values on different rows

A

Alonso

Hi

my problem is that I have in cell A1 (merged with A2) the name of one item
on B1 is the stock
and in B2 is the number of items we need for the month
on A3 (merged with A4) is another item
again, b3 = stock
B4 = needed

What I want to do is
(1) to sum all the items on stock
(2) to sum all the items needed for that month

one way will be
STOCK =A1+A3+...
NEEDED = A2+A4+...

it's there another, simpler way??
the items could reach a 100, and I don't want to end
STOCK =A1+A3+...+A199
 
P

Peo Sjoblom

Unmerge the cells, never use merged cells except for header in maybe the
first row.
Otherwise you are in for unpleasant surprises down the road. I never use
merged cells anywhere because I
don't need them, you can center across selection to get similar layout but
merged cells will always cause grief whether it is copying and pasting,
sorting or using functions


if you want to add every second cell counting from A1

=SUMPRODUCT(--(MOD(ROW(A1:A199)-ROW(A1),2)=0),A1:A199)


from A2


=SUMPRODUCT(--(MOD(ROW(A1:A199)-ROW(A1),2)=1),A1:A199)

--


Regards,


Peo Sjoblom
 
A

Alonso

Thanks for your reply Peo

2 things
my mistake, the cells to be added are the ones on column B (the quantity)

second: i have to add every THIRD cell
that would be B1+B4+B7+... for stock
and B2+B5+B8+... for needed

right now, using your formula
i get a #VALUE

I don't know where it's the error
 
P

Peo Sjoblom

=SUMPRODUCT(--(MOD(ROW(B1:B199)-ROW(B1),3)=0),B1:B199)

for B1, B4, B7 etc



=SUMPRODUCT(--(MOD(ROW(B1:B199)-ROW(B1),3)=1),B1:B199)


for B2, B5, B8 and so on



--


Regards,


Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top