COUNTIF() with multiple criteria

[Thansal]

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

[Vito]

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.

[Thansal]

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

[SimonCC]

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
>
>