“Count†occurrences of two conditions on the same row

[Mike G.]

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
(e-mail address removed)

Best regards, Mike

 

[Biff]

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................Closed
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