This is trickier than I thought.
In this example cell H3 would be
I tried the following, I think it's close.
If I simply use
In this example cell H3 would be
=sum(B3:G3)
, but I don't always know what the last column will be.Copy of Area 84 IND Sept-October.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | |||
1 | |||||||||
2 | 600336A3 | 600336PL | 600336SSIFU | 600336SSIVD | 600336VIQS | 601036F | Total | ||
3 | 8 | 5 | 7 | 3 | 0 | 6 | |||
4 | 1 | 1 | 0 | 1 | 0 | 0 | |||
5 | 2 | 2 | 0 | 1 | 0 | 0 | |||
6 | 4 | 2 | 0 | 0 | 0 | 0 | |||
7 | 1 | 2 | 0 | 1 | 0 | 1 | |||
8 | 1 | 3 | 0 | 0 | 0 | 0 | |||
9 | 1 | 0 | 0 | 1 | 1 | 1 | |||
10 | 0 | 4 | 3 | 1 | 1 | 2 | |||
11 | 0 | 2 | 0 | 0 | 0 | 0 | |||
12 | 0 | 0 | 6 | 0 | 0 | 1 | |||
13 | 0 | 0 | 1 | 0 | 2 | 1 | |||
14 | 0 | 0 | 0 | 1 | 0 | 1 | |||
15 | 0 | 0 | 0 | 2 | 0 | 0 | |||
report |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B3:G15 | B3 | =COUNTIFS(data!$H:$H,$A3,data!$A:$A,B$2) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
data!_FilterDatabase | =data!$A$3:$AI$3 | B3:G15 |
I tried the following, I think it's close.
VBA Code:
Dim Lcol As Long
Dim rng As Range
Dim rng2 As Range
Lcol = Cells(2, Columns.Count).End(xlToLeft).Column
Set rng = Range("A2")
Set rng2 = rng.Offset(1, Lcol - 1)
rng.Offset(0, Lcol).Value = "Total"
rng.Offset(1, Lcol).Value = "=sum(B3:" & rng2 & ")"
If I simply use
rng2.select
G3 is selected. I think something with my formula "=sum(B3:" & rng2 & ")"
isn't right.