Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF() with multiple criteria
I am looking to pull some data about a section of one of my worksheets.
Specificly I have 3 columns that I want a count of the number of instances of each unique combination in those 3 cells (IE the number of times "1, 0, 0" appears, "1, 1, 0" appears, "1, 1, 1" appears, etc etc). The second part of this would be If it was possible to semi automate this so that I don't have to write a function for each unique series. Thank you very much for any and all help you can offer me. AlexV |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF() with multiple criteria
If you put your combinations in say 3 columns,starting at say Row 2 e.g. D2,E2,F2 = 1,0,0 respectively, D3,E3,F3 = 1,1,0 respectively, etc... Then you can use: =sumproduct(--($A$2:$A$100=D2),--($B$2:$B$100=E2),--($C$2:$C$100=F2)) and copy down. Where A2:C100 contain the combinations. Adjust ranges and references to suit you. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=558876 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF() with multiple criteria
Thank you that is working well for me.
However, if you would not mind I do not realy get exactly what it is doing. I looked up SUMPRODUCT and I see what it does. However I do not know what the "--" operator does (nor do I realy understand how Excel handels multiplying the parts of a one column array). Thank you again! AlexV Vito wrote: If you put your combinations in say 3 columns,starting at say Row 2 e.g. D2,E2,F2 = 1,0,0 respectively, D3,E3,F3 = 1,1,0 respectively, etc... Then you can use: =sumproduct(--($A$2:$A$100=D2),--($B$2:$B$100=E2),--($C$2:$C$100=F2)) and copy down. Where A2:C100 contain the combinations. Adjust ranges and references to suit you. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=558876 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF() with multiple criteria
Unfortunately Excel help only explains the basics of SUMPRODUCT. However,
there's a page with very good explanation of SUMPRODUCT at: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -Simon "Thansal" wrote: Thank you that is working well for me. However, if you would not mind I do not realy get exactly what it is doing. I looked up SUMPRODUCT and I see what it does. However I do not know what the "--" operator does (nor do I realy understand how Excel handels multiplying the parts of a one column array). Thank you again! AlexV Vito wrote: If you put your combinations in say 3 columns,starting at say Row 2 e.g. D2,E2,F2 = 1,0,0 respectively, D3,E3,F3 = 1,1,0 respectively, etc... Then you can use: =sumproduct(--($A$2:$A$100=D2),--($B$2:$B$100=E2),--($C$2:$C$100=F2)) and copy down. Where A2:C100 contain the combinations. Adjust ranges and references to suit you. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=558876 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use multiple criteria with COUNTIF: between dates and not blank | Excel Worksheet Functions | |||
Counting cells using multiple criteria | Excel Worksheet Functions | |||
Multiple Criteria using countif | Excel Worksheet Functions | |||
multiple criteria in one field 4a,4b etc of countif? | Excel Discussion (Misc queries) | |||
Multiple CountIf Criteria | Excel Worksheet Functions |