excel command that counts conditions met in 2 columns?

[brendalw]

is there a form of countif that increments only if conditions are met in two
(or more) columns? e.g., countif(colA = 1 and colB = 2)

 

[Guest]

Hi

You need SUMPRODUCT. Have a look here for some guidance and post back if you
need some more help.
http://www.contextures.com/xlFunctions01.html#SumProduct

Hope this helps.

Andy.

 

[John]

Use SUMPRODUCT
EG:
=SUMPRODUCT((A1:A10=1)*(B1:10=2))

 

[Aladin Akyurek]

Some options...

[1]

=SUMPRODUCT(($A$2:$A$100=1)+0,($B$2:$B$100=2)+0)

[2]

=SUM(IF($A$2:$A$100=1,IF($B$2:$B$100=2,1)))

followed by control+shift+enter.

[3]

C2, copied down:

=A2&"#"&B2

=COUNTIF($C$2:$C4100,"="&1&"#"&2)

[4] Construct a pivot table.