Consulting

Results 1 to 8 of 8

Thread: Using Countifs & Weekdays

  1. #1
    VBAX Newbie
    Joined
    Dec 2015
    Posts
    4
    Location

    Using Countifs & Weekdays

    I have a worksheet that has a column of dates (let's call it A1:A100). The sheet has another column (B) that has values in some of the rows. I would like to calculate, in code, and without iterating through the sheet, how many instances of Mon (or any day of the week) have a value and, as a bonus, sum the values.
    I have tried ApplicationFunction.Sum and .SumProducts and Countifs. Just stumped!l Any help would be appreciated.
    Using Excel 2007.
    Last edited by RickS650; 05-03-2017 at 09:42 AM.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    I would like to calculate, in code, and without iterating through the sheet
    Ya got me!

    Why is that so critical?

    I bet one of our Formula experts could write a cell formula that could do it. I can do that, as long as I use a helper column with a WeekDay function.

    Helper Column (C) Formula ---- "=IF(WEEKDAY(A1,2)=1,1,0)"
    Count of mondays formula -----"=SUM(C1:C16)"
    Sum of Monday Values Formula "=SUMIF(C1:C16,1,B1:B16)"
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Newbie
    Joined
    Dec 2015
    Posts
    4
    Location
    Thanks for the quick response. I already have the cell formula but it got me thinking that there must be a sleeker way. Sum(-- is so close for the weekday bit but doesn't quite work.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    There is probably a CSE formula, but they give me headaches.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Newbie
    Joined
    Dec 2015
    Posts
    4
    Location
    Yes, I got a headache from CSE too. Strange thing is when I pressed CSE I got the #VALUE error, but when I pressed Enter I got a number, just doing the Weekday bit. F9 on parts of the formula produced results as expected. I may try to reproduce what I did and post it here.


    I have now run the macro recorder on the weekday part and got the correct answer:

    Sub Macro1()
    ' Macro1 Macro
    'Range("T2").Select
        Selection.FormulaArray = "=SUM(IF(WEEKDAY(RC[-2]:R[381]C[-2])=4,1,0))"
        Debug.Print Range("T2")
        ' Result is 71 - correct
    End Sub
    but I would like to put the result into a variable (say x) but, of course, I can't because it is an array. This is now personal between me and this macro..... I WILL win!
    Last edited by Aussiebear; 04-27-2023 at 12:14 PM. Reason: Added code tags

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,439
    Location
    You don't need an array formula, SUMPRODUCT does it

    =SUMPRODUCT(--(WEEKDAY(A1:A100)=2),B1:B100)
    or

    =SUMPRODUCT(--(TEXT(A1:A100,"ddd")="Mon"),B1:B100)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,439
    Location
    Quote Originally Posted by RickS650 View Post
    I have now run the macro recorder on the weekday part and got the correct answer:

    Sub Macro1()
    ' Macro1 Macro
    Range("T2").Select
    Selection.FormulaArray = "=SUM(IF(WEEKDAY(RC[-2]:R[381]C[-2])=4,1,0))"
    Debug.Print Range("T2")
    ' Result is 71 - correct
    End Sub
    You rarely need to select

    Range("T2").FormulaArray = "=SUM(IF(WEEKDAY(RC[-2]:R[381]C[-2])=4,1,0))"
    Quote Originally Posted by RickS650 View Post
    but I would like to put the result into a variable (say x) but, of course, I can't because it is an array. This is now personal between me and this macro..... I WILL win!
    You can evaluate the formula

    x = Application.Evaluate("=SUMPRODUCT(--(WEEKDAY(A2:A100)=2),B2:B100)")
    Last edited by Aussiebear; 04-27-2023 at 12:15 PM. Reason: Adjusted the code tags
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Newbie
    Joined
    Dec 2015
    Posts
    4
    Location

    Solved

    By crikey, that's done it! Brilliant, I knew it could be done with a one-liner..

    Thanks very much

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •