How to use the SumIf function when the rows to be summed varies

[pearl]

Greetings,

The code below works just fine to sum up the column once it finds the number of rows to sum.

With ActiveCell
Set Rng = Range(.Offset(-mycount + 1), .End(xlUp))
.Formula = "=SUM(" & Rng.Address(RowAbsolute:=False, _
ColumnAbsolute:=False) & ")"
End With


Now, I would like for the code to look at column "b" and based on what is in that column to include or exclude the number to be summed. I started with a sumif((b:b),""<>Exclude"",etc..The problem is it looks a the entire "b" column and I only want it to look at the range of the rows being summed.

Thank you in advance,
Pearl

 

[MrKowz]

Can you please provide an example of how your data is laid out, and how you determine what range to sum?

 

[pearl]

This is the part of the code that sums columns. The row will vary based on the building so first the code counts how many rows.
Do While bldgname <> ""
Sheets("bldg by bldg summary").Select
Range("a:a").Activate

If (Cells.Find(bldgname)) = bldgname Then
Cells.Find(bldgname).Select
ActiveCell.Offset(1, 0).Select
mycount = ActiveCell.Count + 1

Do While ActiveCell <> ""
ActiveCell.Offset(1, 0).Select
mycount = mycount + 1
Loop

End If

ActiveCell.EntireRow.Select
ActiveCell.EntireRow.Clear


ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Total"

With ActiveCell.Font
.Bold = True
End With

ActiveCell.Offset(0, 4).Select


With ActiveCell
Set Rng = Range(.Offset(-mycount + 1), .End(xlUp))
.Formula = "=SUM(" & Rng.Address(RowAbsolute:=False, _
ColumnAbsolute:=False) & ")"
End With

I would like to change which numbers are summed based on whether column "b" says exclude
<TABLE style="WIDTH: 474pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=632 border=0><COLGROUP><COL style="WIDTH: 191pt; mso-width-source: userset; mso-width-alt: 9073" span=2 width=255><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4352" width=122><TBODY><TR style="HEIGHT: 13.2pt; mso-height-source: userset" height=18><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 191pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 53.4pt; BACKGROUND-COLOR: silver" width=255 height=72 rowSpan=4>Building Name</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 191pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: silver" width=255 rowSpan=4>Include or Exclude from Project</TD><TD class=xl95 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=122></TD></TR><TR style="HEIGHT: 13.2pt; mso-height-source: userset" height=18><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 40.2pt; BACKGROUND-COLOR: silver" width=122 height=54 rowSpan=3>Guaranteed Dollar Savings</TD></TR><TR style="HEIGHT: 13.2pt" height=18></TR><TR style="HEIGHT: 13.8pt" height=18></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>Alpha </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>baseline Adj</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Exclude</TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>-$2,296</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>Lighting</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Include </TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$1,497</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>Mech</TD><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Include </TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>-$16,301</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.8pt; BACKGROUND-COLOR: transparent" height=18></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>Total</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right>-$17,099</TD></TR></TBODY></TABLE>