1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Count IF Help

Discussion in 'Ask an Excel Question' started by paulcherianc, Aug 7, 2017.

  1. paulcherianc

    paulcherianc Member

    Messages:
    75
    I need to have a Count If formula that will populate with some conditions. Can anyone help me to fix this?

    Logic: If the date on A:A (Staff List - On Board) is not equal to EOMONTH, then populate 1st day of the month of the given date, if not, 1st day of next month)- I need to integrate this condition on the Count If formula.

    Sample file attached.

    Attached Files:

    Last edited: Aug 7, 2017
  2. rahulshewale1

    rahulshewale1 Active Member

    Messages:
    178
    Hii,

    Using helper column...
    Try below formula

    =COUNTIF('Staff List - On Board'!D2:D7,B2)

    Thanks.
    Rahul Shewale

    Attached Files:

  3. paulcherianc

    paulcherianc Member

    Messages:
    75
    This is not what I am looking for.
    I need to exclude the helper column and integrate the formula to the count if.
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,652
    Hi ,

    Try this in B3 and copy across :

    =COUNTIFS('Staff List - On Board'!$A$2:$A$6, "<" & EOMONTH(B$2, 0), 'Staff List - On Board'!$A$2:$A$6, ">=" & B$2) + COUNTIF('Staff List - On Board'!$A$2:$A$6, EOMONTH(B$2, -1))

    Narayan
    paulcherianc likes this.
  5. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,254
    Or, try.....

    In B3, formula copy across right to C3.

    =COUNTIFS('Staff List - On Board'!$A$2:$A$6,">="&EOMONTH(B$2,-1),'Staff List - On Board'!$A$2:$A$6,"<="&EOMONTH(B$2,0)-1)

    Regards
    Bosco
    Last edited: Aug 8, 2017
    paulcherianc likes this.
  6. paulcherianc

    paulcherianc Member

    Messages:
    75
    Cool ! Bravo guys! Thanks a lot!
  7. paulcherianc

    paulcherianc Member

    Messages:
    75
    Can you please explain the logic behind this formula.
  8. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,254
    The idea is to count the number of dates fall between "End of last month" and "End of this month-1"

    In C3 formula :

    =COUNTIFS($A$2:$A$6,">="&EOMONTH(C2,-1),$A$2:$A$6,"<="&EOMONTH(C2,0)-1)

    And, in C2 : 01/09/2017

    C3 become >>

    =COUNTIFS($A$2:$A$6,">=31/08/2017",$A$2:$A$6,"<=29/09/2017")

    The desired result >>

    =3 (The result of Countifs criteria range : 31/8/2017 to 29/9/2047)

    Regards
    Bosco
    paulcherianc likes this.
  9. paulcherianc

    paulcherianc Member

    Messages:
    75
    Many thanks!

Share This Page