€œ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

"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