Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

marked as duplicate by CharlieRB, Synetech, Mokubai, Ellesa, Dennis Jul 21 '12 at 14:09

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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

1 Answer 1

up vote 3 down vote accepted

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


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

Adjust as needed.

Edit: For any number of columns, you may try:


Note that this will report two times a line which would have 1 in E and F.

share|improve this answer
Thanks. Is there a way to avoid this multiple +? Because I posted a simplified version of the problem here. – RoflcoptrException Jul 19 '12 at 12:33
@RoflcoptrException: I edited my reply with an alternative. – Karolos Jul 19 '12 at 12:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.