Excel 2007 CountIF and SumIF 2

[herkiefan]

Hi,

I am having trouble using the CountIf function when the range I reference contains lables that begin with <= and >=.

I recieve a large amount of data that has a column called "age range". The lowest range is <=29 and the highest range is >= 70. I do not have the underlying age that corresponds to the age range (some HR bull that doesn't allow me to know the age of employees, just their range).

When I use the CountIf and SumIf functions and try to summarize the data based on the age ranges, I get 0 for both <=29 and >=70. Is there a way to make the formula work? I've looked through the previous posts and didn't find anything helpful.

Thanks,

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[xlhelp]

My data is in B2:B6.
=SUM(IF($B$2:$B$6=B2,1,0)) as an array formula works.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

Hi,

You never actually posted ANY of your formulas! How do you expect a cogent answer? But I'll try.
[tt]
=COUNTIF(age_range,"<=29")
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[herkiefan]

I apologize for not posting my formulas.

Age Range = $BY$6:$BY$15000
Criteria = CK6 the label contained is "<=29".

Formula and Result:
=COUNTIF($BY$6:$BY$15000,CK6)= 0
=COUNTIF($BY$6:$BY$15000,"<=29")= 0

=SUM(IF($BY$6:$BY$15000=CK6,1,0)) = 1,756 (correct)
I was also able to modify your formula to include a sum of hours worked.
Thanks xlhelp

So how come Excel doesn't recogize that <=29 = <=29 in the COUNTIF function but it does in the IF function?



“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[SkipVought]

Please post some ACTUAL VALUES from this column.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[herkiefan]

Values from my age range column:

Age Range
<= 29
30 to 34
35 to 39
40 to 44
45 to 49
50 to 54
55 to 59
60 to 64
65 to 69
70+
Unknown



“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[SkipVought]

THERE is your problem.
[tt]
=SUMPRODUCT(--($BY$6:$BY$15000="<= 29"))
=SUMPRODUCT(--($BY$6:$BY$15000="70+"))
[/tt]
Here's what happened. "<= 29" looks like a NUMERIC CRITERIA in SUMIF and COUNTIF. You have TEXT data, not numeric!



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[herkiefan]

Thanks Skip,

works like a charm now.

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein