Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2007 CountIF and SumIF 2

Status
Not open for further replies.

herkiefan

Technical User
Oct 13, 2006
97
US
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
 
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
 


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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 






Please post some ACTUAL VALUES from this column.



Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 



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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top