CODEWITHSUNDEEP
excel
Matwill
Matwill

Reputation: 1

CountIf cell value meets a condition

Hi I have a row of calculated values that I need to count based on a condition.

I have the conditions working in conditional formatting so that is the condition is met the cell changes color, however I need to find the totals of each color.

=AND($V1/$Z1>0.5, $V1/$Z1<=0.79)

so for this I want to only count values if this statement is true

=COUNTIF(Z:Z, (AND($V1/$Z1>0.5, $V1/$Z1<=0.79)))

currently this returns 0 when there is 10 values that meet this criteria

does anyone know if countif can be used like this?

Upvotes: 0

Views: 273

Answers (2)

teylyn
teylyn

Reputation: 35990

This can be done without a helper column using the Sumproduct() function.

=SUMPRODUCT(--($V$1:$V$100/$Z$1:$Z$100>0.5),--($V$1:$V$100/$Z$1:$Z$100<=0.79))

or

=SUMPRODUCT(($V$1:$V$100/$Z$1:$Z$100>0.5)*($V$1:$V$100/$Z$1:$Z$100<=0.79))

This will count all instances where the values in V1 to V100 divided by the values in Z1 to Z100 are between 0.5 and 0.79

Note that SumProduct is essentially an array formula (even though it does not require confirmation with Ctrl-Shift-Enter, but it evaluates each range as an array), so you don't want to use it with whole column references. If you want to retain calculation speed, make sure to refer to used rows only, not gazillions of empty cells.

Upvotes: 0

Jake Bourne
Jake Bourne

Reputation: 763

I can see your issue, I've had a play around and am unable to crack it, if you wish to keep it in a singular cell you'll probably need to go down the route of an array formula. Its probably complicating it too much and you should look to have a helper column,

in Column AA, have the formula =$v1/$z1, then for your count write:

=countifs(AA:AA,">0.5",AA:AA,"<=0.79")

Upvotes: 1

Related Questions