COUNTIF or SUMPRODUCT?

[potter007]

Hello all

I have table of data and i'm after details from one column to give me a total count. The data in column from row 9 to 125 is a mixture of percentages. What I'm trying to get is a count of the range that is <10%, 10.01%-25%, 25%-50%, 50%-75% & 75%-100% so that i can display this in a new worksheet to give a pie chart of the data.

So far I have been able to do the <10% results using COUNTIF but when I apply it to the others I'm getting percentage results rather than counts.

Here is an example of the formula I used:

=COUNTIF(D9:D125,">10.01%)-COUNTIF(D9:D125,"<25%")

Can anyone guide me as to where I'm going wrong?

Thanks

 

[stilesbn]

If you are using 2007 you can use a COUNTIFS

=COUNTIFS(D9:D125,">" & 0.1001,D9:D125,"<=" & 0.25)

 

[potter007]

If you are using 2007 you can use a COUNTIFS

=COUNTIFS(D9:D125,">" & 0.1001,D9:D125,"<=" & 0.25)

It's 2003 version, tried your formula without s on the end but no result unfortunately.

 

[barry houdini]

Your original approach is OK, but you need to reverse < in the second COUNTIF, try this to count any percentage >=10% but < 25%

=COUNTIF(D9:D125,">=10%")-COUNTIF(D9:D125,">=25%")

You are counting all the %s above 10% and then subtarcting the number above 25% thereby getting a count of all those between

 

[Asator]

But if he doesn't want .1 included and does want .25 included... ">" & .1 and ">" & .25

What surprised me was that this actually returned #VALUE errors:
{=COUNTIF(IF((range>.1)*(range<=.25),range,FALSE),TRUE)}
{=COUNTIF(IF((range>.1)*(range<=.25),range,0),1)}

 

[barry houdini]

Well maybe, Asator....here's what potter007 says

.....get is a count of the range that is <10%, 10.01%-25%, 25%-50%, 50%-75% & 75%-100%....

If you take that literally then 10% exactly doesn't get counted in any range, and 25%, 50% and 75% exactly will all be counted twice. I took a chance and assumed that wasn't the requirement.

If you understand the principle then the specifics can be adjusted as required.

Another way that might be more transparent

would be to use SUMPRODUCT, i.e.

=SUMPRODUCT((D9:D125>=10%)*(D9:D125<25%))

That should give exactly the same result as the previous formula I suggested

 

[potter007]

Your original approach is OK, but you need to reverse < in the second COUNTIF, try this to count any percentage >=10% but < 25%

=COUNTIF(D9:D125,">=10%")-COUNTIF(D9:D125,">=25%")

You are counting all the %s above 10% and then subtarcting the number above 25% thereby getting a count of all those between

Many Thanks, I thought I was on the right tracks but just couldn't work out where I was going wrong.