COUNTIFS - Excel 07

[Evan]

Hello,
Using this example set of data:
A B
John 1
Mary 6
Bill 7
John 5
Rob 10
Jim 2

How would a formula look that wants to count anything in column B greater
than 5, and anything in column A that is equal to either John, Mark, or Jim?
I know I could do 3 separate countifs and add them together, but my real
excel sheet is much more complicated than this. Just wondering if there is
some sort of "OR" function that can be used in this situation with countifs.

Thanks!

 

[T. Valko]

List the names to count for in a range of cells:

J1 = John
J2 = Mark
J3 = Jim

This will work:

=SUMPRODUCT(COUNTIFS(A2:A7,J1:J3,B2:B7,">5"))

But this is better:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,J1:J3,0))),--(B2:B7>5))

 

[Herbert Seidenberg]

Excel 2007 PivotTable
AND/OR
No formulas needed.
http://www.mediafire.com/file/d3onzgddtmn/10_07_09b.xlsx