Countif help

[AM7788]

Dear all,

I need assistance in doing a count using countif to specify the range of percentage of my data.

Meaning;

For -ve value:
1) more than -50%
2) -30% to -50%, both inclusive
3) -10% (inclusive) to less than -30%
4) less than -10% to -1%

For +ve value:
1) more than 50%
2) 30% to 50%, both inclusive
3) 10% (inclusive) to less than 30%
4) Less 10% to 0 %

Thanks and I hope look forward to your reply soon.

AM

 

[marka87uk]

Hi,

=COUNTIF(C:C,"<=-0.50") would calculate any less or equal to -50%. Simply adjust the value to suit.

To count between a range you can minus one COUNTIF from another.

 

[anandvarma]

try this for positives

Excel Workbook
	A	B	C	D
1	15%		more than 50%	3
2	30%		between 30% to 50%	3
3	25%		between 10% to 30%	3
4	40%		between 0% to 10%	0
5	50%
6	60%
7	75%
8	99%
9	22%
Sheet1

 

[AM7788]

Hi mark,

Thanks for your help. Apparently, I have this set of formulas but my problem is, there are a few data that have -50% and it only counted once but not twice. I need to exclude the 2 data of -50% to get my count of "less than -50%". Similar things happen for the rest of the range.

=COUNTIF($AL$16:$AL$169,"< -50%")
=COUNTIF($AL$16:$AL$169,"< -30%")-H7
=COUNTIF($AL$16:$AL$169,"< -10%")-SUM(H7:H8)
=COUNTIF($AL$16:$AL$169,"< 0%")-SUM(H7:H9)

H7 = the count of <-50%
H7 to H8: the count of the first two rows
H7 to H9: the count of the first three rows.

AM7788

 

[AM7788]

Hi anandvarma,

This works well for the positive number. How about the negative number? Any idea?

Thanks
AM7788

 

[anandvarma]

try this for negative

Excel Workbook
	A	B	C	D
1	-15%		more than 50%	3
2	-30%		between 30% to 50%	3
3	-25%		between 10% to 30%	3
4	-40%		between 0% to 10%	0
5	-50%
6	-60%
7	-75%
8	-99%
9	-22%
Sheet1

 

[AM7788]

Hi anandvarma,

Thanks for all the solutions for my qns though. Logically speaking, the way you did the formula is correct, but when I try to use the same set of forumlas to my data, I'm unable to get the correct count. I'm wondering what is wrong with my set of data.

AM7788

 

[AM7788]

Dear all,

Thanks for all the assistance. I have used pivot table to do the counting instead. Sincere thanks to all those who help.

AM7788