# Excel: Count occurence in multiple rows with condition [duplicate]

Possible Duplicate:
Count number of rows with conditions met in two columns

I have a table in Excel that looks like this:

``````A                 1        2
B                 2
A                 1        2
C                 3        1
D                 1        3
A                 1        4
``````

In column A i have a set of words. Some of them are repeated. In columns E and F I have two other sets. Now I want to count all events where A is in column A and 1 is in column E or F.

I tried something like this

``````=COUNTIF(A1:A6, "A", E1:F6, "1")
``````

Bu this does not work. Any hints to to solve the problem?

-

## marked as duplicate by CharlieRB, Synetech, Mokubai♦, Kaze, DennisJul 21 '12 at 14:09

Exact duplicate - Count number of rows with conditions met in two columns – CharlieRB Jul 19 '12 at 12:22

First of all, in order to have multiple conditions, you need to use `COUNTIFS` (an extra s).

You can then achieve what you want using

`=COUNTIFS(A:A,"A",E:E,1)+COUNTIFS(A:A,"A",F:F,1)-COUNTIFS(A:A,"A",E:E,1,F:F,1)`

which adds the contributions from E to that of F and subtracts the lines where both E and F are 1.

`=SUMPRODUCT((A:A="A")*(E:F=1))`