Excel COUNTIF Not Working

Question

I have two columns that are adjacent to each other and I need to count the rows in those columns that are less than a value when divided. The function

=COUNTIF(BG172:BG41552/BH172:BH41552,"<=.1")

does not work and gives errors.

EDIT: Per my comment below, a more clarified question: I mean to take each individual cell in column BG and divide it by each adjacent cell in BH so BG172/BH172 and if that answer is <= .10 I want to count it and have that done for the whole range of both columns.

Can you just do a helper column next to BH and just run the countif on that? — BruceWayne, Apr 14 '17 at 17:47

score 1 · Accepted Answer · answered Apr 14 '17 at 17:50

1

I'm not sure if I fully understand your question, but see if this is what you are looking for:

=IF(BG172/BH172<=0.1, COUNT(BG172:BG41552) + COUNT(BH172:BH41552), "NOT <= 0.1")

This divides the two adjacent cells, checks to see if that is <= 0.1, if it is the output is the count of rows in the two adjacent columns, added together; if it isn't the output prints "NOT <= 0.1".

Alternatively, if you want to sum the two adjacent columns then divide to see if the value is <= 0.1, this is how you would do that:

=IF(sum(BG172:BG41552)/sum(BH172:BH41552)<=0.1, COUNT(BG172:BG41552) + COUNT(BH172:BH41552), "NOT <= 0.1")

answered Apr 14 '17 at 17:50

DukeW

36
7

Sorry if my question is a little vague, I mean to take each individual cell in column BG and divide it by each adjacent cell in BH so BG172/BH172 and if that answer is <= .10 I want to count it and have that done for the whole range of both columns. – Brad Bones Apr 14 '17 at 17:58
Depending on which column you want to count, this equation is the one you want: =IF(BG172/BH172<=0.1, COUNT(BG172:BG41552), "NOT <= 0.1") - This counts the rows in the BG column. =IF(BG172/BH172<=0.1, COUNT(BH172:BH41552), "NOT <= 0.1") - This counts the rows in the BH column. – DukeW Apr 14 '17 at 18:03

score 0 · Answer 2 · answered Apr 14 '17 at 17:48

You cannot perform any calculation in the range argument within the countif function.

You may try something like this... (Untested)

This is an Array Formula which will require a special key stroke Ctrl+Shift+Enter instead of Enter alone.

=SUM(IFERROR(--(((BG172:BG41552)/(BH172:BH41552))<0.1),0))

Confirm with Ctrl+Shift+Enter

Excel COUNTIF Not Working

2 Answers2