Summing dynamic Excel column and count

[Yohanis]

Hi all

I wonder if anyone can help me on how to sum a dynamic excel columns and rows (where the size of the data varies from file to file)

In addition, Using the same result (say the column Sum list) , I would like to count those between say 100 and 150 and show their percentage on a new excel sheet.
The original data which produced the column sum is on another sheet.

Please see the example on the new excel sheet where i am hoping to produce
Thank you in advance.




SUM​







125​








120​



Percentage between 100 and 150 is 28.5%




86​








52​








25​








99​








253​

[Paul_Hossler]

Added 150 and 151 for testing

30924

[Yohanis]

Thank you Paul and I am very Sorry my question was not clear.
What i want to do is that: I have an excel file where I have rows and columns of data where the size of the data changes from time to time.

As a first step i want to Sum those numbers horizontally (does not include the first row and the first column) and put the result in a newly created Sheet
Next step is from the newly created Sheet which the size also changes depending on the original excel file, i want to extract those number between 100 and 150
Next is the percentage I, think i can do that part as it is only one number.

I have also attached and example

[Paul_Hossler]

Not sure I understand

Using WS formulas you need to put the SUM into column A, but the summary formulas can use the entire column, so you don't need to change those

30927


If you just want the percentage, you can use a user defined function. Look on "Data"

Other than that, maybe some of the formula gurus has an idea



Option Explicit


Function PC(r As Range, Optional nLow As Long = 100, Optional nHigh As Long = 150) As Double
Dim r1 As Range, r2 As Range
Dim i As Long, j As Long, n As Long, nCount As Long
Dim v As Variant

'Current REgion doesn't work in functions
Set r1 = r.Parent.Cells(1, r.Column)
Set r2 = r1.End(xlDown).End(xlToRight)

v = Range(r1, r2).Value

For i = LBound(v, 1) + 1 To UBound(v, 1)
n = 0
For j = LBound(v, 2) To UBound(v, 2)
n = n + v(i, j)
Next j


If n >= nLow And n <= nHigh Then nCount = nCount + 1


Next i


PC = nCount / (UBound(v, 1) - 1)


End Function

[Yohanis]

Thank you very much. Actually the percentage part is the last thing I want to do after extracting the Sum value in column A.
So, the first step i have a problem is how to generate those "Sum" values from the other sheet called "Data" and place them where they are now on "Sheet1"
The column and rows data size on the "Data" sheet changes from time to time.
Thank you

[Aussiebear]

Welcome to VBAX Yohanis. To solve any mis understanding here , could you please attach a sample workbook?

[Paul_Hossler]

Thank you very much. Actually the percentage part is the last thing I want to do after extracting the Sum value in column A.
So, the first step i have a problem is how to generate those "Sum" values from the other sheet called "Data" and place them where they are now on "Sheet1"
The column and rows data size on the "Data" sheet changes from time to time.
Thank you

You can do it with a macro, but I don't know any way to have =SUM() WS formulas generated based on adds/deletes/changes on 'Data'



Option Explicit


Sub AddSums()
Dim r As Range, r1 As Range
Dim i As Long


With Worksheets("Sheet1")
Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)).ClearContents
End With

With Worksheets("Data")
Set r = .Cells(1, 1).CurrentRegion

For i = 2 To r.Rows.Count

Set r1 = .Cells(i, 1)

Worksheets("Sheet1").Cells(i, 1).Formula = "=SUM('" & r1.Parent.Name & "'!" & r1.Resize(1, r.Columns.Count).Address & ")"
Next i
End With
End Sub

[Yohanis]

Sub AddSums() works!!!This is brilliant and thank you very much Paul. one more thing is that I also want to integrate the function which you gave me above to calculate the percentage . So how can i make that function part of Sub AddSums () so it will also calculate the percentages evetime the sum and sheet changes

Thank you very much again

[Paul_Hossler]

The formulas in G2, G3, and G4 handle any changes in Col A automatically

30930

Do you mean something else?

[Yohanis]

Hi Paul.
Yes the thing is that Sheet1 where the SUM is appearing is created dynamically using the code below. Now once Sheet1 created then the Sub AddSums() you gave me will be executed. Following that I want to calculate the percentage.




Sheets(ActiveSheet.Name).Name = "Sheet1"


Thank you for your time Paul

[Aussiebear]

Maybe try this


Worksheets("Sheet1").Cells(i,4).Formula = "=Cells(i,3) & "/" & Cells(i,2)"

[Paul_Hossler]

You didn't say that you were creating the summary sheet on the fly


Option Explicit

Const valLow As Long = 100
Const valHigh As Long = 150


Sub AddSums()
Dim r As Range, r1 As Range
Dim i As Long
Dim wsData As Worksheet, wsSummary As Worksheet

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True


Worksheets.Add.Name = "Summary"
Set wsSummary = Worksheets("Summary")

wsSummary.Cells(1, 1).Value = "Sums"


With Worksheets("Data")
Set r = .Cells(1, 1).CurrentRegion

For i = 2 To r.Rows.Count

Set r1 = .Cells(i, 1)

wsSummary.Cells(i, 1).Formula = "=SUM('" & r1.Parent.Name & "'!" & r1.Resize(1, r.Columns.Count).Address & ")"
Next i
End With


wsSummary.Cells(1, 2).Formula = "=COUNTIFS($A:$A,"">=" & valLow & """,$A:$A,""<=" & valHigh & """)/COUNT($A:$A)"
wsSummary.Cells(1, 2).NumberFormat = "#0%"


End Sub

[p45cal]

Cross posted https://chandoo.org/forum/threads/vba-to-sum-dynamic-row-and-column.54108/

[Aussiebear]

Disappointing to read that....

[Yohanis]

Thank you this will solve the function issue.

[Aussiebear]

Hang on a second..... Since we are hanging the washing out to dry. How about a simple apology for cross posting, or a second one for not notifying that you had cross posted the issue else where? You've been given assistance here by volunteers, yet you think you can ignore the issue?

[Yohanis]

Sure . I apologize for both. Trust me that is an innocent mistake (just being human).

[Aussiebear]

Thank you for standing up and taking ownership.