Conditional Countif

[Guest]

I am trying to check two conditions in one worksheet and if true report in
another. So for example in worksheet "ABC" I want to count !B1:!B20 if it
equals "apples" and !C1:!C20 if it equals "oranges". If both conditions are
true I want to return 1 to the answer in worksheet "XYZ".
Thanks

 

[Peo Sjoblom]

=SUMPRODUCT(--(B1:B20="apples"),--(C1:C20="oranges"))

replace the hardcoded values with cell references when you can put the
criteria
that way you don't need to edit the formula if you change the criteria

 

[Guest]

I would be inclined to us a SumProduct like this...
=SUMPRODUCT(--($B$1:$B$20="Apples"), --($C$1:$C$20="Oranges"))
Check out this link for a further explanation...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html