Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing non-contiguous array cells
NEED TO ADD THE FOLLOWING. 3 ROWS SEPERATE THEM & ANY NUMBER OF ROWS CAN BE
ADDED: 1 1 1 1 1 1 3 ROWS BLANK 2 2 2 2 2 2 3 ROWS BLANK 3 3 3 3 3 3 THANKS raj |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing non-contiguous array cells
Try:
Sub AddThree() Dim Last As Long Dim x As Long Application.ScreenUpdating = False Last = Cells(Rows.Count, 1).End(xlUp).Row For x = Last To 3 Step -1 If Cells(x, 1).Value = Cells(x - 1, 1).Value Then GoTo Again End If Cells(x, 1).Resize(3, 1).Insert Shift:=xlDown Again: Next x Application.ScreenUpdating = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "WRC" wrote in message ... NEED TO ADD THE FOLLOWING. 3 ROWS SEPERATE THEM & ANY NUMBER OF ROWS CAN BE ADDED: 1 1 1 1 1 1 3 ROWS BLANK 2 2 2 2 2 2 3 ROWS BLANK 3 3 3 3 3 3 THANKS raj |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing non-contiguous array cells
Why can't you just do:
=SUM(range) and ensure that your range exceeds the number of rows you have now? For example: =SUM(A1:A1000) Blank rows will not cause a problem with this. Hope this helps. Pete On Nov 5, 8:25 pm, WRC wrote: NEED TO ADD THE FOLLOWING. 3 ROWS SEPERATE THEM & ANY NUMBER OF ROWS CAN BE ADDED: 1 1 1 1 1 1 3 ROWS BLANK 2 2 2 2 2 2 3 ROWS BLANK 3 3 3 3 3 3 THANKS raj |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing non-contiguous array cells
Thanks.
let me know if you know any fomula or function mentod? thanks "Sandy Mann" wrote: Try: Sub AddThree() Dim Last As Long Dim x As Long Application.ScreenUpdating = False Last = Cells(Rows.Count, 1).End(xlUp).Row For x = Last To 3 Step -1 If Cells(x, 1).Value = Cells(x - 1, 1).Value Then GoTo Again End If Cells(x, 1).Resize(3, 1).Insert Shift:=xlDown Again: Next x Application.ScreenUpdating = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "WRC" wrote in message ... NEED TO ADD THE FOLLOWING. 3 ROWS SEPERATE THEM & ANY NUMBER OF ROWS CAN BE ADDED: 1 1 1 1 1 1 3 ROWS BLANK 2 2 2 2 2 2 3 ROWS BLANK 3 3 3 3 3 3 THANKS raj |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing non-contiguous array cells
I have data inbetween.
I need to add all ones+twos+threes...... thanks "Pete_UK" wrote: Why can't you just do: =SUM(range) and ensure that your range exceeds the number of rows you have now? For example: =SUM(A1:A1000) Blank rows will not cause a problem with this. Hope this helps. Pete On Nov 5, 8:25 pm, WRC wrote: NEED TO ADD THE FOLLOWING. 3 ROWS SEPERATE THEM & ANY NUMBER OF ROWS CAN BE ADDED: 1 1 1 1 1 1 3 ROWS BLANK 2 2 2 2 2 2 3 ROWS BLANK 3 3 3 3 3 3 THANKS raj |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing non-contiguous array cells
So you don't have blank rows then? What do you have?
Pete On Nov 5, 9:19 pm, WRC wrote: I have data inbetween. I need to add all ones+twos+threes...... thanks "Pete_UK" wrote: Why can't you just do: =SUM(range) and ensure that your range exceeds the number of rows you have now? For example: =SUM(A1:A1000) Blank rows will not cause a problem with this. Hope this helps. Pete On Nov 5, 8:25 pm, WRC wrote: NEED TO ADD THE FOLLOWING. 3 ROWS SEPERATE THEM & ANY NUMBER OF ROWS CAN BE ADDED: 1 1 1 1 1 1 3 ROWS BLANK 2 2 2 2 2 2 3 ROWS BLANK 3 3 3 3 3 3 THANKS raj- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing non-contiguous array cells
Just select them with the mouse, there is no built in function that will do
this unless you have a pattern that is the same for all data. If you want to exclude every 7th row you can use =SUMPRODUCT(--(MOD(ROW(A1:A30),7)<0),A1:A30) -- Regards, Peo Sjoblom "WRC" wrote in message ... I have data inbetween. I need to add all ones+twos+threes...... thanks "Pete_UK" wrote: Why can't you just do: =SUM(range) and ensure that your range exceeds the number of rows you have now? For example: =SUM(A1:A1000) Blank rows will not cause a problem with this. Hope this helps. Pete On Nov 5, 8:25 pm, WRC wrote: NEED TO ADD THE FOLLOWING. 3 ROWS SEPERATE THEM & ANY NUMBER OF ROWS CAN BE ADDED: 1 1 1 1 1 1 3 ROWS BLANK 2 2 2 2 2 2 3 ROWS BLANK 3 3 3 3 3 3 THANKS raj |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing non-contiguous array cells
I need to add all the $ i.e ( Qual Hardware ($'s) +No Charge Deliverables
($'s)+ DAT Hardware ($'s)+Outside Lab ($'s)+Purchased Items ($'s) ) for diffrent projects: Project:X Proj Eng (Hrs) 1 Design/Drafting (Hrs) 4 Analysis (Hrs) 5 Test Tech (Hrs) 1 Qual Hardware ($'s) 1 No Charge Deliverables ($'s) 1 DAT Hardware ($'s) 1 Outside Lab ($'s) 1 Purchased Items ($'s) 1 Project: y Proj Eng (Hrs) 1 Design/Drafting (Hrs) 4 Analysis (Hrs) 1 Test Tech (Hrs) 2 Qual Hardware ($'s) 2 No Charge Deliverables ($'s) 2 DAT Hardware ($'s) 2 Outside Lab ($'s) 2 Purchased Items ($'s) 2 Project:k Proj Eng (Hrs) 2 Design/Drafting (Hrs) 4 Analysis (Hrs) 3 Test Tech (Hrs) 1 Qual Hardware ($'s) 3 No Charge Deliverables ($'s) 3 DAT Hardware ($'s) 3 Outside Lab ($'s) 3 Purchased Items ($'s) 3 ................... Thanks "Pete_UK" wrote: So you don't have blank rows then? What do you have? Pete On Nov 5, 9:19 pm, WRC wrote: I have data inbetween. I need to add all ones+twos+threes...... thanks "Pete_UK" wrote: Why can't you just do: =SUM(range) and ensure that your range exceeds the number of rows you have now? For example: =SUM(A1:A1000) Blank rows will not cause a problem with this. Hope this helps. Pete On Nov 5, 8:25 pm, WRC wrote: NEED TO ADD THE FOLLOWING. 3 ROWS SEPERATE THEM & ANY NUMBER OF ROWS CAN BE ADDED: 1 1 1 1 1 1 3 ROWS BLANK 2 2 2 2 2 2 3 ROWS BLANK 3 3 3 3 3 3 THANKS raj- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing non-contiguous array cells
Pattern is I have 3 rows blank between projects & number rows between Qual
Hardware of project 1 to Qual Hardware of project 2 remains constant.....etc. thanks "Peo Sjoblom" wrote: Just select them with the mouse, there is no built in function that will do this unless you have a pattern that is the same for all data. If you want to exclude every 7th row you can use =SUMPRODUCT(--(MOD(ROW(A1:A30),7)<0),A1:A30) -- Regards, Peo Sjoblom "WRC" wrote in message ... I have data inbetween. I need to add all ones+twos+threes...... thanks "Pete_UK" wrote: Why can't you just do: =SUM(range) and ensure that your range exceeds the number of rows you have now? For example: =SUM(A1:A1000) Blank rows will not cause a problem with this. Hope this helps. Pete On Nov 5, 8:25 pm, WRC wrote: NEED TO ADD THE FOLLOWING. 3 ROWS SEPERATE THEM & ANY NUMBER OF ROWS CAN BE ADDED: 1 1 1 1 1 1 3 ROWS BLANK 2 2 2 2 2 2 3 ROWS BLANK 3 3 3 3 3 3 THANKS raj |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing non-contiguous array cells
I think it would be better to have hours in one column (which you
might then multiply by an hourly rate) and dollars in another column, but given what you have you could make use of SUMIF like this: =SUMIF(A1:A1000,"Qual Hardware ($'s)",B1:B1000) + SUMIF(A1:A1000,"No Charge Deliverables ($'s)",B1:B1000) + SUMIF(A1:A1000,"DAT Hardware ($'s)",B1:B1000) + SUMIF(A1:A1000,"Outside Lab ($'s)",B1:B1000) + SUMIF(A1:A1000,"Purchased Items ($'s)",B1:B1000) Now that I've typed that out I think you could shorten it to: =SUMPRODUCT((A1:A1000<"")*(RIGHT(A1:A1000,5)="($' s)")*(B1:B1000)) I've assumed 1000 rows of data - adjust as necessary. Hope this helps. Pete On Nov 5, 9:38 pm, WRC wrote: I need to add all the $ i.e ( Qual Hardware ($'s) +No Charge Deliverables ($'s)+ DAT Hardware ($'s)+Outside Lab ($'s)+Purchased Items ($'s) ) for diffrent projects: Project:X Proj Eng (Hrs) 1 Design/Drafting (Hrs) 4 Analysis (Hrs) 5 Test Tech (Hrs) 1 Qual Hardware ($'s) 1 No Charge Deliverables ($'s) 1 DAT Hardware ($'s) 1 Outside Lab ($'s) 1 Purchased Items ($'s) 1 Project: y Proj Eng (Hrs) 1 Design/Drafting (Hrs) 4 Analysis (Hrs) 1 Test Tech (Hrs) 2 Qual Hardware ($'s) 2 No Charge Deliverables ($'s) 2 DAT Hardware ($'s) 2 Outside Lab ($'s) 2 Purchased Items ($'s) 2 Project:k Proj Eng (Hrs) 2 Design/Drafting (Hrs) 4 Analysis (Hrs) 3 Test Tech (Hrs) 1 Qual Hardware ($'s) 3 No Charge Deliverables ($'s) 3 DAT Hardware ($'s) 3 Outside Lab ($'s) 3 Purchased Items ($'s) 3 .................. Thanks "Pete_UK" wrote: So you don't have blank rows then? What do you have? Pete On Nov 5, 9:19 pm, WRC wrote: I have data inbetween. I need to add all ones+twos+threes...... thanks "Pete_UK" wrote: Why can't you just do: =SUM(range) and ensure that your range exceeds the number of rows you have now? For example: =SUM(A1:A1000) Blank rows will not cause a problem with this. Hope this helps. Pete On Nov 5, 8:25 pm, WRC wrote: NEED TO ADD THE FOLLOWING. 3 ROWS SEPERATE THEM & ANY NUMBER OF ROWS CAN BE ADDED: 1 1 1 1 1 1 3 ROWS BLANK 2 2 2 2 2 2 3 ROWS BLANK 3 3 3 3 3 3 THANKS raj- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing an array of cells absolute value | Excel Worksheet Functions | |||
Summing non contiguous cells | Excel Discussion (Misc queries) | |||
Summing cells in array | Excel Worksheet Functions | |||
summing non contiguous ranges | Excel Discussion (Misc queries) | |||
Summing non-contiguous cells | Excel Worksheet Functions |