Another Excel question, this time countif and if?

Bleek · 13 Jan 2011 at 19:04

I have googled this but still can't get it to work correctly.

I have two columns, one with "Up" or "Down" and another column with "W" or "L".

What I'm trying to do is counts the following:

Total Up and W

I found the following formula but it doesn't work - =COUNT(IF(H5:H39="Up",IF(Q5:Q39="W")))

As per the other post, any assistance is much appreciated.

james.miller · 13 Jan 2011 at 19:10

you'd use countifs for that

eg:

=Countifs(H5:h39,"up",Q5:A39,"W")

Bleek · 13 Jan 2011 at 19:28

I tried that, it just says #NAME?

james.miller · 13 Jan 2011 at 19:30

oooh you i guess you arent using 2007 or 2010?

ok, erm. let me think lol

james.miller · 13 Jan 2011 at 19:35

=SUMPRODUCT(--(H5:H39="up"),--(I2:I100="w"))

try that

Bleek · 13 Jan 2011 at 20:05

Brilliant that worked a treat!

I've being fiddling with this for a few hours - if I were anywhere near Dunstable I'd buy you a pint.

james.miller · 13 Jan 2011 at 23:00

haha no problem, glad i could help