CODEWITHSUNDEEP
arraysexcel-formulaexcel-2010worksheet-functioncountif
Joshua Viele
Joshua Viele

Reputation: 27

Two column conditional sum

I have a table with data in two columns with three possible responses: sim, nao, and N/A. I would like to to know the number of times a row contains either a sim or a nao in either column, i.e. the sum of this formula: 

=IF(OR(A2="sim",A2="nao",B2="sim",B2="nao"),1,0)

In the table below it would make the result return 8: 

Column 1 |  Column 2
---------------------
N/A      |    Sim
---------------------
Sim      |    Sim
---------------------
Sim      |    Sim
---------------------
N/A      |    Nao
---------------------
Sim      |    Sim
---------------------
N/A      |    N/A
---------------------
Sim      |    Sim
---------------------
Sim      |    Sim
---------------------
Sim      |    Sim

Upvotes: 1

Views: 121

Answers (4)

pnuts
pnuts

Reputation: 59475

As I understand the question, it might be restated as "how many rows am I left with if I exclude those with N/A in both columns of any row": 

=COUNTA(A:A)-COUNTIFS(A:A,"N/A",B:B,"N/A")

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34230

I agree a helper column is the easiest approach, you could also try this:-

=SUM(IF((A2:A10="sim")+(B2:B10="sim")+(A2:A10="nao")+(B2:B10="nao"),1))

which must be entered as an array formula using Ctrl-Shift-Enter.

The 'OR' condition is implemented as a sum. Each inner bracket which evaluates to 'TRUE' will contribute 1 to the sum of each row in an array. If the sum of the inner brackets evaluates to one or more, the IF statement will return '1' for that row.

The SUM then totals the number of rows containing a '1', to give the number of matching rows, in this case 8.

To count matching rows in the whole of the first two columns, use:-

=SUM(IF((A:A="sim")+(B:B="sim")+(A:A="nao")+(B:B="nao"),1))

enter image description here

The N/A above is a text string, not a #N/A error.

Upvotes: 0

Carl
Carl

Reputation: 36

If I understand your question correctly you would want to use COUNTIFS Here is a link to an article by ablebits that explain how to use them.

https://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/

Upvotes: 1

cybujan
cybujan

Reputation: 1047

add a formula = if(or()...) in the third column and then below the data range you can use =countif() function to count all cells with '1'

Upvotes: 0

Related Questions