Take the 2-minute tour ×
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.

1  
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

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

Adjust as needed.

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

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

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.