I have 2 columns as attached file. I want to count how many Sales Rep. I and Sales Rep. II based on section criteria.
For example, I can count manually that there're 3 Sales Rep. I in Sales HCM and 2 Sales Rep. II in Sales HCM. Or, there're 3 Sales Rep. I in Sales Central I
Does anyone know how to use a function instead? Please advise
This can be used:
=SUMPRODUCT(--($B$2:$B$17="Sales Rep. I"),--($A$2:$A$17="Sales HCM"))
If you would use Excel 2007 COUNTIFS() can also be used.
=COUNTIFS(A2:A17,"Sales HCM",B2:B17,"Sales Rep. I")
Looking for great solutions but hate waiting?
Seach this Forum through Google
www.Google.com (e.g. +multiple +IF site:excelforum.com/excel-general/ )
www.Google.com (e.g. +fill +combobox site:excelforum.com/excel-programming/ )
Ave,
Ricardo
It works. Many thanks
In your formula there are two -- . What do they stand for? Pls advise
Xl2003 and before did not have the COUNTIFS so one had to use the sumproduct. the --() produces a MASK with 0,1. These two are multiplied so the end result is an AND function.
Example: A1:A3 have values 1,2,3
sumproduct(--(A1:A3)>1,--(A1:A3)<3) = sumproduct(--(1,2,3)>1,--(1,2,3)<3) = sumproduct(--(False,True,True),--(True,True,False)) = sumproduct({0,1,1},{1,1,0}) = sumproduct(0,1,0) = 1
Looking for great solutions but hate waiting?
Seach this Forum through Google
www.Google.com (e.g. +multiple +IF site:excelforum.com/excel-general/ )
www.Google.com (e.g. +fill +combobox site:excelforum.com/excel-programming/ )
Ave,
Ricardo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks