how to Count cells in column B based on date in column A
Results 1 to 5 of 5

Thread: how to Count cells in column B based on date in column A

  1. #1
    New Member
    Join Date
    Jul 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default how to Count cells in column B based on date in column A

    Hello,
    in cells A2:A200 i have dates, than in cells B2:B200 it either "TRUE or FALSE"
    im looking for a formula in cell D4 to count how many TRUE cells per month in cell C3
    see below how i need it to calculate


    A B C D
    1 Date Value Month to count count
    2 1/1/2019 TRUE 1/1/2019 2
    3 1/15/2019 TRUE 2/1/2019 1
    4 2/1/2019 TRUE 3/1/2019 1
    5 2/28/2019 FALSE
    6 3/1/2019 TRUE
    7 3/31/2019 FALSE
    8

    i figured out the same idea is sum function as follows but need your help count function

    in cell D2 =SUMIFS($B$2:$B$7,$A$2:$A$7,">="&C2,$A$2:$A$7,"<="&EOMONTH(C2,0))
    in cell D3 =SUMIFS($B$2:$B$7,$A$2:$A$7,">="&C3,$A$2:$A$7,"<="&EOMONTH(C3,0))

    A B C D
    1 Date Value Month to sum sum
    2 1/1/2019 1 1/1/2019 4
    3 1/15/2019 3 2/1/2019 2
    4 2/1/2019 1 3/1/2019 5
    5 2/28/2019 1
    6 3/1/2019 2
    7 3/31/2019 3
    8

    Thank You

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    300
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to Count cells in column B based on date in column A

    Hi, you are very close, this should work:

    ABCD
    1DateValueMonth to countcount
    21/1/2019TRUE1/1/20192
    31/15/2019TRUE2/1/20191
    42/1/2019TRUE3/1/20191
    52/28/2019FALSE
    63/1/2019TRUE
    73/31/2019FALSE

    Sheet6



    Worksheet Formulas
    CellFormula
    D2=COUNTIFS($A$2:$A$7,">="&C2,$A$2:$A$7,"<="&EOMONTH(C2,0),$B$2:$B$7,TRUE)

    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    300
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to Count cells in column B based on date in column A

    Just another option:

    Code:
    =SUMPRODUCT(($A$2:$A$7 >= C2)*($A$2:$A$7 <= EOMONTH(C2,0))*($B$2:$B$7=TRUE))
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    New Member
    Join Date
    Jul 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to Count cells in column B based on date in column A

    Quote Originally Posted by Aryatect View Post
    Just another option:

    Code:
    =SUMPRODUCT(($A$2:$A$7 >= C2)*($A$2:$A$7 <= EOMONTH(C2,0))*($B$2:$B$7=TRUE))

    Thank You, amazing! worked well....
    I like the =sumproduct formula better, because im able to count column c as well
    just 1 more question Mr Genius can this formula work with count cell with any value? not a specific like true, false, or at specified in the formula?

  5. #5
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    300
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to Count cells in column B based on date in column A

    Hi,

    Glad could help! It can work with any value, instead of specifying TRUE or FALSE in a formula you can refer it to any cell, extending your problem where say column B had names and we wanted to find result for specific name then the same formula can be reused as shown below:

    ABCDEF
    1DateValueMonth to countcountNameABC
    21/1/2019ABC1/1/20192
    31/15/2019ABC2/1/20191
    42/1/2019DEF3/1/20191
    52/28/2019ABC
    63/1/2019ABC
    73/31/2019DEF

    Sheet2



    Worksheet Formulas
    CellFormula
    D2=SUMPRODUCT(($A$2:$A$7 >= C2)*($A$2:$A$7 <= EOMONTH(C2,0))*($B$2:$B$7=$F$1))

    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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