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

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

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

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