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)

Please Login or Register  to view this content.

or if preferred using double unary

Please Login or Register  to view this content.

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