Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count occurrences of two conditions on the same row
Please provide a simple solution to count occurrences of two conditions on
the same row. I have a weekly spreadsheet with a variable number of rows (approx. 300 rows). I would like to look in each populated row for a match that, Row X, Cell 6 = Chicago + Cell 10 = Closed Row X, Cell 6 = NYRK + Cell 10 = Closed Row X, Cell 6 = Boston + Cell 10 = Closed Etc. Row X, Cell 6 = Chicago + Cell 10 = Open Row X, Cell 6 = NYRK + Cell 10 = Open Row X, Cell 6 = Boston + Cell 10 = Open Etc. I have fixed number of 22 cities and 2 status' (Closed / Open) I want the total number of matches <cities + Closed reported at the bottom of the spreadsheet. For example: CLOSED NYRK 56 Chicago 87 Los Angeles 132 Boston 12 Boise 20 Etc. OPEN NYRK 125 Chicago 99 Los Angeles 245 Boston 45 Boise 36 Etc. Please reply and/or email me your solutions Best regards, Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Count" occurrences of two conditions on the same row
Hi!
I'm assuming that: Row X, Cell 6 means column F Row X, Cell 10 means column J Setup a table like this: .......A.....................B.................... ...C............ 1..........................Open................Clo sed 2 NYRK 3 Chicago 4 Los Angeles 5 Boston 6 Boise Enter this formula in B2: =SUMPRODUCT(--($F$2:$F$10=$A2),--($J$2:$J$10=B$1)) Copy across to C2 then down. Adjust the references to suit. Biff "Mike G." wrote in message ... Please provide a simple solution to "count" occurrences of two conditions on the same row. I have a weekly spreadsheet with a variable number of rows (approx. 300 rows). I would like to look in each populated row for a match that, Row X, Cell 6 = "Chicago" + Cell 10 = "Closed" Row X, Cell 6 = "NYRK" + Cell 10 = "Closed" Row X, Cell 6 = "Boston" + Cell 10 = "Closed" Etc. Row X, Cell 6 = "Chicago" + Cell 10 = "Open" Row X, Cell 6 = "NYRK" + Cell 10 = "Open" Row X, Cell 6 = "Boston" + Cell 10 = "Open" Etc. I have fixed number of 22 cities and 2 status' (Closed / Open) I want the total number of matches "<cities + Closed" reported at the bottom of the spreadsheet. For example: CLOSED NYRK 56 Chicago 87 Los Angeles 132 Boston 12 Boise 20 Etc. OPEN NYRK 125 Chicago 99 Los Angeles 245 Boston 45 Boise 36 Etc. Please reply and/or email me your solutions Best regards, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort 2 or more occurrences | Excel Worksheet Functions | |||
Count using 2 conditions, one of which being a "less than or equal to" - URGENT | Excel Discussion (Misc queries) | |||
COUNT using multiple conditions | Excel Discussion (Misc queries) | |||
CountIf Conditions / Occurrences | Excel Worksheet Functions | |||
Counting occurrences of multiple conditions | Excel Discussion (Misc queries) |