Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF with 2 conditions/columns
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. -- Rich http://www.richdavies.com/online-mor...calculator.htm http://www.richdavies.com/saving-money.htm ** Posted from http://www.teranews.com ** |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF with 2 conditions/columns
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")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF with 2 conditions/columns
"Max" wrote in message ... 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")) -- 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...... -- Rich http://www.richdavies.com/online-mor...calculator.htm http://www.richdavies.com/saving-money.htm ** Posted from http://www.teranews.com ** |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF with 2 conditions/columns
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"),D 2:D10) .. 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). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF with 2 conditions/columns
"Max" wrote in message ... 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"),D 2:D10) 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. -- http://www.richdavies.com/saving-money.htm -- ** Posted from http://www.teranews.com ** |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF with 2 conditions/columns
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)),D2: D10+0) 1. The addition of a zero in: D2:D10+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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
Countif depending on the conditions across multiple columns | Excel Worksheet Functions | |||
Using countif with 2 conditions | Excel Worksheet Functions | |||
COUNTIF 2 conditions | Excel Worksheet Functions | |||
COUNTIF for 2 conditions | Excel Worksheet Functions |