| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple countif lookup
Superb. Thanks a lot.
"Dave Peterson" wrote: =SUMPRODUCT(--(A1:A100="bruce"),--(ISNUMBER(G1:G100)),--(G1:G1000)) /COUNTIF(A1:A100,"Bruce") (all one cell) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Bruce Hancock wrote: Ok here goes, I have a table that has a salespersons name in column A (rows 2-62) and a profit value in column G (rows 2-62 also). I want to count only the specific salespersons name and values greater than 0 and return a percentage figure. For example salesman A has sold 22 cars (22 seperate entries in column A) and on 11 of these he has sold accessories (11 0 entries and 11 =0 entries in column G). I need a formulae that will return 50% (or 0.50) by counting the number of entries greater than 0 in column G and dividing it by the number of times salesman A appears in column A. HELP PLEASE. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup or countif | Excel Worksheet Functions | |||
| Multiple Lookup and CountIF | Excel Worksheet Functions | |||
| countif counta with multiple lookup criteria | Excel Discussion (Misc queries) | |||
| Random and Lookup and Countif | Excel Discussion (Misc queries) | |||
| LOOKUP and COUNTIF | Excel Worksheet Functions | |||
![Bruce Hancock[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
