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

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

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

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

summing non-contiguous array cells
 

It seems that I misinterpreted your requirements, sorry.

--
Regards,

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

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

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 -

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

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 -

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

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 -