1. ## 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

2. 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

3. Hi Kevin

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

Howard

4. Hi Howard

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

zeddy

5. Hi Zeddy

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

Howard

6. 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. 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

8. 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. 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
•