Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum values on different rows
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum values on different rows
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 "Alonso" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum values on different rows
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 "Peo Sjoblom" wrote: 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 "Alonso" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum values on different rows
=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 "Alonso" wrote in message ... 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 "Peo Sjoblom" wrote: 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 "Alonso" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Match Values in Rows with Partial Values in Columns | Excel Worksheet Functions | |||
How do I add rows at fixed intervals-add values after every 5 Rows | Excel Discussion (Misc queries) | |||
Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
counting rows with same values for multiple values | New Users to Excel |