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

 

[Joe4]

Which version of Excel are you using?
If 2007 or higher, check out the COUNTIFS function, which allows more than one criteria.
Otherwise, I would recommend using SUMPRODUCT.

 

[JackDanIce]

Hi Joe, Excel 2003, sorry forgot to specify

 

[Joe4]

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

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

 

[JackDanIce]

Not quite working. Have specified the range exactly and used ABS(O18:O440)>0.001 (i.e. above 0.1%) but answer is zero and there are definitely values above 0.1%

Any thoughts?

 

[RoryA]

Are you sure the cells in E contain just Total (no leading or trailing spaces) and that the values in O are actually numbers?

 

[JackDanIce]

Rorya, really!

Yes in column E, value is definitely "Total", double checked in a separate column with

=And(LEN(E18)=5,E18="Total")

It may be because of how values in column O are created, they're formula's, i.e.
In O18

=T18/$B$9

 

[Coryj5]

What do the "--" do in the formula?

 

[RoryA]

Should be fine then. Though I note you did initially say 10%, now you say 0.1% - do you have enough decimals displayed to be sure your values actually are greater than the criterion?

 

[JackDanIce]

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