CODEWITHSUNDEEP
excelfunctioncountif
Brad Bones
Brad Bones

Reputation: 3

Excel COUNTIF Not Working

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.

Upvotes: 0

Views: 624

Answers (2)

DukeW
DukeW

Reputation: 36

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")

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

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

Upvotes: 0

Related Questions