• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Weekday & weekend dates sum

A

Abhijeet

Active Member
Hi
I have data i want weekday & weekend sum of the units of particular numbers and same w Count of those i have mention expected result is attach file so give me macro for this macro will get this result
 

Attachments

  • If Date.xlsx
    11.7 KB · Views: 4
Hi Abhijeet,

Try below code:

Code: 
Sub addCount()

Dim lr As Long, lr1 As Long
Dim sumW As Long, sumW1 As Long, countW As Long, countW1 As Long

lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr1 = Sheets("Sheet1").Cells(Rows.Count, 7).End(xlUp).Row

sumW = 0
sumW1 = 0
countW = 0
countW1 = 0
For i = 3 To lr1
    For j = 3 To lr
        If Cells(j, 3) = Cells(i, 7) Then
            If Cells(j, 1) = "Sunday" Or Cells(j, 1) = "Saturday" Then
                sumW1 = sumW1 + Cells(j, 4)
                countW1 = countW1 + 1
            Else
            sumW = sumW + Cells(j, 4)
            countW = countW + 1
            End If
        End If
        Next j
            Cells(i, 8) = sumW
            Cells(i, 9) = sumW1
            Cells(i, 10) = countW
            Cells(i, 11) = countW1
       
        sumW = 0
        sumW1 = 0
        countW = 0
        countW1 = 0
       
      Next i
           
           

End Sub

Regards,
 
Even simpler, use a PivotTable, and group the days into 'Weekday' and 'Weekend'. See attached.
 

Attachments

  • Sum by weekday and weekend.xlsx
    15.3 KB · Views: 4
Are you getting any error? Can you run the macro step by step by pressing F8 and see what values variables are getting?

Regards,
 
@Abhijeet

It' working on my system. I posted the code only after checking it.

Click any where inside the code and just press F8 multiple times to see what are the values of variables SumW, SumW1, CountW, CountW1 you can also add WATCH through debug menu to these variable. and than do step execution of the code.

Regards,
 
I got problem every thing i deleted Number Unique Number also so no result is show can u please give that number is also run with macro
 
In column G Number these numbers i want pull with help of macro.Now Macro not pull from data manually put these numbers
 
You must log in or register to reply here.
Back
Top