CountIf problem based on a character ? 3

[Mizzness]

All.

I have a table using the CountIf function in Sheet1 to capture data from Sheet2.
The table is setup as:

Fails By Age
<8 (col.K) 8-15 (col.L) 16-30 (col.M) >30 (col.N)

The CountIf function works for cols. L & M but not for K & N.
Is this because the criteria contains the "<" & ">" characters ?
I have also checked to see that the format matches.
Is there a way around this ?

Please advise.
Thanx.

 

[Kurupt55]

Try
COUNTIF(Sheet2!a1:a21,"<=15")-K1
COUNTIF(Sheet2!a1:a21,"<=30")-K1-L1
This will do what you want (obviously change the range to suit your situation)

Impossible is Nothing

 

[Mizzness]

Kurupt,

Maybe I wasn't clear at first.

The "<" & ">" characters are part of the heading used in the criteria of the CountIf function.
The CountIf function is referencing the following formula:
=IF(C13<8,"<8",IF(AND(C13>7,C13<16),"8-15",IF(AND(C13>15,C13<31),"16-30",IF(C13>30,">30"))))
Example: Cell C13=6. Then the formula will give me <8 as the answer. I then use the CountIf function to bucket the data under the four different headings.
For some reason the CountIf function does not work when either the "<" or ">" characters are referenced. Even the "?" character works.

I'm not sure what you mean by subtracting data in your formula.

Thanx.

 

[Mizzness]

Resolved this issue via a pivot table but would like to know if there is a flaw or issue with Excel that prevents you from using the "<" or ">" characters as a reference or part of a reference in a function.

 

[AnotherHiggins]

Your formula in the last post handles 'betweens' correctly:
[tab]=IF(C13<8, "<8", IF(AND(C13 > 7, C13 < 16), "8-15", IF(AND(C13 > 15, C13 < 31),"16-30",IF(C13 > 30, ">30"))))

But then the CountIf uses the results of the above formula as the criteria. So let's look at the four different CountIf formulas you're constructing:[ul]
[li]=CountIf(YourRange, "<8")[/li]
[li]=CountIf(YourRange, "8-15")[/li]
[li]=CountIf(YourRange, "16-30")[/li]
[li]=CountIf(YourRange, ">30")[/li]
[/ul]
"8-15" and "16-30" are text strings, not ranges. Even if Excel did know that you wanted something other than a string, it would look at those as mathematical formulas (yielding -7 and -14, respectively), not ranges.

In general, you want to construct the type of formula you're after like this:
[tab]=CountIf(YourRange, "<=30") - CountIf(YourRange, "<=15")

So Excel is behaving exactly as one should expect.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[bluedragon2]

If you put ?8 and ?30, it will work for you.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Kurupt55]

I understand What you are getting at now and have had a play with the formula.
=COUNTIF(YourRange,"<8") will count all cells that have a value less than 8 but will not include text strings.
=COUNTIF(YourRange,"8-15") will count all cells that have the text string 8-15 hence this will work in your situation.
strangely enough =COUNTIF(YourRange,"<A") will count all cells containing < or >, but not a<a>???.
in any case you would be better getting your count from your source data on Sheet2 using the formulas anotherhiggins or I posted, or stick with the pivot table solution.

Impossible is Nothing

 

[Loomah]

ooooo, this will give me something to think about today!

in the meantime here's an alternative method
=SUMPRODUCT(--(A1:A48="<8"))

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bluedragon2]

Misread your orig question:

You can use:

=COUNTIF(A1:A5,"=<8")

This will count the string <8

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Mizzness]

Loomah / Blue,

Both solutions work. Thanx.

To quote Skip: "There is more than one way to skin a cat."
A star for both of you.