Results 1 to 9 of 9

Thread: Countifs

  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Countifs

    I have debtors ageing in Col K and Categories ( U or N) in Col J.

    I would like to count how many debtors per category falls into the following bands.Where there are Negative values in Col I , these can be ignored. I know that Countifs can do this but not sure how to set it up

    0 -7
    8-30
    31-60
    >60

    For Eg Cat U 8-30 is 2 in the attached example.

    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    If you aren't counting the negatives, U 8-30, I believe counts to 1.

    Starting in N1, I wrote the following:

    U
    =COUNTIFS($J$2:$J$16,N$1,$I$2:$I$16,">=0",$K$2:$K$ 16,">=0",$K$2:$K$16,"<=7")
    =COUNTIFS($J$2:$J$16,N$1,$I$2:$I$16,">=0",$K$2:$K$ 16,">=8",$K$2:$K$16,"<=30")
    =COUNTIFS($J$2:$J$16,N$1,$I$2:$I$16,">=0",$K$2:$K$ 16,">=31",$K$2:$K$16,"<=60")
    =COUNTIFS($J$2:$J$16,N$1,$I$2:$I$16,">=0",$K$2:$K$ 16,">60")

    Then, filled it across to O, with N in O1

    N
    =COUNTIFS($J$2:$J$16,O$1,$I$2:$I$16,">=0",$K$2:$K$ 16,">=0",$K$2:$K$16,"<=7")
    =COUNTIFS($J$2:$J$16,O$1,$I$2:$I$16,">=0",$K$2:$K$ 16,">=8",$K$2:$K$16,"<=30")
    =COUNTIFS($J$2:$J$16,O$1,$I$2:$I$16,">=0",$K$2:$K$ 16,">=31",$K$2:$K$16,"<=60")
    =COUNTIFS($J$2:$J$16,O$1,$I$2:$I$16,">=0",$K$2:$K$ 16,">60")

    Is that what you were trying to calculate?

    COUNTIFS uses range, criteria, range, criteria, etc.

    Kevin
    Last edited by kweaver; 2013-11-16 at 00:07.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Kevin

    Thanks for the help & explanation. Attached please find spreadsheet with your formulas. I get zero for each formula. Please check & correct

    Howard
    Attached Files Attached Files
    Last edited by HowardC; 2013-11-16 at 07:24.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    ..here's another way of doing it (see attached).

    zeddy
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the help. Have been through your formula and fully understand the logic

    Howard

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    Top marks! I was hoping you would do that.
    Kevin's method works of course.
    But I started by working backwards, from counting those over 60 days.
    Then those over 30 days includes those over 60 days, so I just subtracted that over 60 days calc, etc etc.
    Also, I used the whole column for simplicity (since you would likely have additional rows etc).
    Finally, by 'pushing' your start header row down to row 10 (I nearly always use that row as my 'header row'), it leaves space at the top to include vba buttons etc, or, as in this case, a place for the ageing analysis.
    I use 10 as my header row because it is easy to count how many records I have by looking at the last data row and subtracting a nice easy 10.

    zeddy

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    ..the reason why you had issues with Kevins solution is you didn't put the required U in cell [n1] and N in cell [o1]
    ..see attached

    zeddy
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    You didn't reference the U or the N. Here's a modified version of the four lines for "U"

    =COUNTIFS($J$2:$J$16,"U",$I$2:$I$16,">=0",$K$2:$K$ 16,">=0",$K$2:$K$16,"<=7")
    =COUNTIFS($J$2:$J$16,"U",$I$2:$I$16,">=0",$K$2:$K$ 16,">=8",$K$2:$K$16,"<=30")
    =COUNTIFS($J$2:$J$16,"U",$I$2:$I$16,">=0",$K$2:$K$ 16,">=31",$K$2:$K$16,"<=60")
    =COUNTIFS($J$2:$J$16,"U",$I$2:$I$16,">=0",$K$2:$K$ 16,">60")

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Kevin

    Thanks for the help. Much appreciated, The Countifs formula is a great feature for multiple criteria

    Howard

Posting Permissions

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