Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFS - Excel 07
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFS - Excel 07
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:B75)) -- Biff Microsoft Excel MVP "Evan" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFS - Excel 07
Excel 2007 PivotTable
AND/OR No formulas needed. http://www.mediafire.com/file/d3onzg...10_07_09b.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007: complex COUNTIFS() | Excel Worksheet Functions | |||
Excel 2003 version of Countifs | Excel Discussion (Misc queries) | |||
countifs on earlier version of excel | Excel Worksheet Functions | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
countifs - what is instead in excel 2000??? | Excel Worksheet Functions |