PDA

View Full Version : [SOLVED] Help with COUNTIFS please



JonnyB
08-25-2015, 08:09 AM
Hi,

Trying to work out how to get the following result using COUNTIFS

=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,"??")

So its looking for PP in the 1st range & the second range has a formula =DATEDIF(W3,TODAY(),"Y") - W3 being date of birth
I need to count ages 0-10, 11-20, 21-30 etc - so need a number of separate COUNTIFS formulae.

So if W3 = 15/08/1999 age would be 16 and would figure in the 11-20 count.

Question is what do I put in the ?? to get me the right result?

Any help appreciated
thanks
Jon

mancubus
08-25-2015, 01:34 PM
try:


=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,">=0",$X$3:$X$411,"<=10")

p45cal
08-25-2015, 02:21 PM
just be aware of the >= and<= in formulae such as this; the implication being that the next range might be:
=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,">=10",$X$3:$X$411,"<=20")
in which case any exact 10s would be counted twice.
Or it might be:
=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,">=11",$X$3:$X$411,"<=20")
where anything greater than 10 and less than 11 won't be counted at all.

One solution is to have one of the terms not have an = sign, so a sequence might be:
=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,">0",$X$3:$X$411,"<=10")
=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,">10",$X$3:$X$411,"<=20")
=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,">20",$X$3:$X$411,"<=30")

or perhaps:
=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,">=0",$X$3:$X$411,"<10")
=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,">=10",$X$3:$X$411,"<20")
=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,">=20",$X$3:$X$411,"<30")

Either way, there is no overlap of criteria, and no gaps either.

try:
=COUNTIFS($J$3:$J$411,"PP",$X$3:$X$411,">=0",$X$3:$X$411,"<=10")

JonnyB
08-26-2015, 02:43 AM
Thanks guys - I almost had this - just didn't put the = behind the < or > .

I'm now tinkering with a SUMIFS formula to bring in the total value (in column K3:K411) for each of the ranges

If you could help further that would be great

So PP first criteria, age range second & then 3rd is the totla value of each element in the second range

Had a crack but struggling a bit

thanks again
Jon

JonnyB
08-26-2015, 03:06 AM
=SUMIF($J$3:$J$411,"PP",$K$3:$K$411) gives me the total for the whole age range.

Adding the additional age criteria from the COUNTIFS (and making it SUMIFS) tells me there aren't enough arguments

So column J is where the 'PP' is , column X is where the age is & column K is the value


thanks for your help
regards
Jon

p45cal
08-26-2015, 10:48 AM
SUMIFS v. SUMIF: range to sum is first argument rather than the last.
SUMIFS v. COUNTIFS: SUMIFS needs a range to sum

try:
=SUMIFS($K$3:$K$411,$J$3:$J$411,"PP",$X$3:$X$411,">0",$X$3:$X$411,"<=10")

Sounds like a pivot table could get you what you want more quickly.

JonnyB
08-27-2015, 12:51 AM
Thanks M8 - should have tried putting the amounts in the formula first - good learning stuff.

regards
Jon