summing non-contiguous array cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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
 
It seems that I misinterpreted your requirements, sorry.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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
 
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
 
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
 
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
 
Back
Top