Countif

[JackDanIce]

Hi,

I have a table of data where column E contains various words and column O contains positive and negative values

I'd like a COUNTIF formula that counts any row where the value in E = "Total" and the absolute value in O is above 0.1 (or 10%)

Unsure how to define this in my criteria, should I be using a COUNTIF or SUMPRODUCT formula?

Tips/pointers/solutions, all welcome.

Thanks in advance,
Jack

 

[JackDanIce]

Sorry, yes meant to be above 10% but since the first SUMPRODUCT formula returned 0, I changed it to 0.1% to see if it the answer changed/increased (it should be 50, for the whole population, but still returns 0)

 

[Coryj5]

It should look something like this (adjust your range accordingly):

=SUMPRODUCT(--(E1:E8="Total"),--(ABS(O1:O8)>0.1))

Turns TRUE, FALSE values in the test into 0s and 1s so they can be numerically evaluated, rather than a logical test

Ah, ok. thanks!

 

[JackDanIce]

Works now, had a typo, apologies. Thanks Joe and Rory