Results 1 to 9 of 9
Thread: Countifs

20131115, 22:16 #1
 Join Date
 Feb 2008
 Posts
 1,526
 Thanks
 137
 Thanked 8 Times in 8 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
830
3160
>60
For Eg Cat U 830 is 2 in the attached example.
Your assistance in this regard is most appreciated

20131115, 23:03 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,560
 Thanks
 43
 Thanked 72 Times in 68 Posts
If you aren't counting the negatives, U 830, 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.
KevinLast edited by kweaver; 20131115 at 23:07.

20131115, 23:38 #3
 Join Date
 Feb 2008
 Posts
 1,526
 Thanks
 137
 Thanked 8 Times in 8 Posts
Hi Kevin
Thanks for the help & explanation. Attached please find spreadsheet with your formulas. I get zero for each formula. Please check & correct
HowardLast edited by HowardC; 20131116 at 06:24.

20131116, 05:07 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,345
 Thanks
 162
 Thanked 621 Times in 590 Posts
Hi Howard
..here's another way of doing it (see attached).
zeddy

20131116, 06:25 #5
 Join Date
 Feb 2008
 Posts
 1,526
 Thanks
 137
 Thanked 8 Times in 8 Posts
Hi Zeddy
Thanks for the help. Have been through your formula and fully understand the logic
Howard

20131116, 08:16 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,345
 Thanks
 162
 Thanked 621 Times in 590 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

20131116, 08:23 #7
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,345
 Thanks
 162
 Thanked 621 Times in 590 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

20131116, 08:26 #8
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,560
 Thanks
 43
 Thanked 72 Times in 68 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")

20131116, 09:34 #9
 Join Date
 Feb 2008
 Posts
 1,526
 Thanks
 137
 Thanked 8 Times in 8 Posts
Hi Kevin
Thanks for the help. Much appreciated, The Countifs formula is a great feature for multiple criteria
Howard