COUNTIF with 2 conditions/columns

[Rich]

Hi,

Column A = Date, Column B = Day of Week , Column C= Weather (Sunny, Rainy
etc)

I know I can use COUNTIF to count the number of wednesdays in B, or the
number of Rainy's in C.

I'm trying to count the number of Rainy Wednesdays. COUNTIF seems the way to
go, but clearly isn't.

Help appreciated.

 

[Max]

I'm trying to count the number of Rainy Wednesdays. COUNTIF seems the way

to go, but clearly isn't.

For 2 or more simultaneous criteria, think: sumproduct
Eg: = sumproduct((C2:C10="Rainy")*(B2:B10="Wednesday"))

 

[Rich]

For 2 or more simultaneous criteria, think: sumproduct
Eg: = sumproduct((C2:C10="Rainy")*(B2:B10="Wednesday"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik

Max,

That works great, although interesting to note I have to use c2:c10 rather
than specify the column c:c.

My next challenge is with column D= Rainfall in mm

I want to add up the total rainfail on Rainy Wednesdays......

 

[Max]

My next challenge is with column D= Rainfall in mm

I want to add up the total rainfail on Rainy Wednesdays......

Easy, just strap it on like this:
= sumproduct((C2:C10="Rainy")*(B2:B10="Wednesday"),D210)

.. I have to use c2:c10 rather than specify the column c:c

Yes, at least in xl03 (my ver). And since sumproduct is calc-intensive, I'd
just use the smallest range large enough to cover the max expected extent of
data, for performance's sake. But I heard (I don't have) that xl07 allows
entire col ranges (eg: c:c) to be used, albeit whether the performance
aspects in doing so are tolerable is not known (I have not come across
discussions on this as yet).

 

[Rich]

Easy, just strap it on like this:
= sumproduct((C2:C10="Rainy")*(B2:B10="Wednesday"),D210)

Thanks, Max

I'm struggling to make that one work, it's returning "-" in every line. I'm
actually using a reference to a cell instead of the text "Wednesday" and
suspect thats the issue.

 

[Max]

I'm struggling to make that one work, it's returning "-" in every line.

I'm actually using a reference to a cell instead of the text "Wednesday"
and suspect thats the issue.

2 possibilities that I can think of:
1. The data in col D are text numbers or a mix of text/real numbers
2. The cell that you're pointing col B to (eg: E1) contains an input
which has extraneous white spaces that's throwing the matching off

Try this revised version:
=SUMPRODUCT((C2:C10="Rainy")*(B2:B10=TRIM(E1)),D210+0)

1. The addition of a zero in: D210+0
will coerce all text numbers (if any) to real numbers
2. Using TRIM around the input cell E1 in: B2:B10=TRIM(E1)
will remove any extraneous white spaces (leading/trailing spaces)
inadvertently keyed in