COUNTIF with two criteria?

[sstecklein]

Need help...

I need to return the number of occurrences in which two criteria are met:

Instances in which A1:A2793 read "A1" AND in which the numerical value in C1:C2793 is >50.

Any ideas?

[DonkeyOte]

If using XL2007 as implied in profile see COUNTIFS function

=COUNTIFS(A1:A2793,"A1",C1:C2793,">50")

[Palmetto]

Instances in which A1:A2793 read "A1" AND in which the numerical value in C1:C2793 is >50.

By "read A1" do you mean cell A1? (I'm being too literal probably, but you don't mean the text "A1"?)

=SUMPRODUCT(($A$1:$A$2793=$A$1)*((COUNTIF(C1:C2793,">50")/(COUNTIF(C1:C2793,">50")))))

[DonkeyOte]

Palmetto, I don't quite follow the SUMPRODUCT, shouldn't it be (A1 issue excepted)

=SUMPRODUCT(($A$1:$A$2793=$A$1)*($C$1:$C$2793>50))

or if preferred using double unary

=SUMPRODUCT(--($A$1:$A$2793=$A$1),--($C$1:$C$2793>50))

again though just to reiterate if indeed you are running XL07 and backwards compatibility is not an issue then use COUNTIFS in preference to SUMPRODUCT.

[Palmetto]

DO,

I'm getting the correct result with my formula.

Both of the formulas you gave return a result that is off (less) by one. Don't know the reason for this, though.

[DonkeyOte]

The correct answer is 9, there are ten instances of Bob in A the first of which has a value of 50 assigned to it - ie should be excluded.

[Palmetto]

Yeah, caught me not paying attention again! ">50" means just that. (at myself).