Counting "rows", i.e. simultaneous criteria for multiple cells
 

I'm trying to count the number of cells meeting certain criteria, but by
applying multiple criteria to more than one column of cells. Basically,
this would be the equivalent of row counting.

So, assuming I have Column A having letters "A", "B", "C", etc., and
Column B having numbers 1, 2, 3, etc., I'd like to be able to count
unique combinations of values in the two columns.

# ColA ColB
1 A 1
2 A 1
3 A 2
4 A 2
5 B 1
6 B 2
7 C 1
8 D 1

Something like COUNTIF(A1:A8,"A"+B1:B8,1).
The result would be 2, as there are 2 rows with ColA="A", ColB=1.

Microsoft only shows examples of doing this with one range. I'm hoping
there's a relatively simple notation for combining ranges, without
having to dig into VBScript. Any ideas? Thanks!


--
gkline
------------------------------------------------------------------------
gkline's Profile: http://www.excelforum.com/member.php...o&userid=16599
View this thread: http://www.excelforum.com/showthread...hreadid=314634

=SUMPRODUCT(--(A1:A8="A"),--(B1:B8=1))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"gkline" wrote in message
...

I'm trying to count the number of cells meeting certain criteria, but by
applying multiple criteria to more than one column of cells. Basically,
this would be the equivalent of row counting.

So, assuming I have Column A having letters "A", "B", "C", etc., and
Column B having numbers 1, 2, 3, etc., I'd like to be able to count
unique combinations of values in the two columns.

# ColA ColB
1 A 1
2 A 1
3 A 2
4 A 2
5 B 1
6 B 2
7 C 1
8 D 1

Something like COUNTIF(A1:A8,"A"+B1:B8,1).
The result would be 2, as there are 2 rows with ColA="A", ColB=1.

Microsoft only shows examples of doing this with one range. I'm hoping
there's a relatively simple notation for combining ranges, without
having to dig into VBScript. Any ideas? Thanks!


--
gkline
------------------------------------------------------------------------
gkline's Profile:
http://www.excelforum.com/member.php...o&userid=16599
View this thread: http://www.excelforum.com/showthread...hreadid=314634

and for details on how the SUMPRODUCT function works check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers
JulieD

"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A1:A8="A"),--(B1:B8=1))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"gkline" wrote in message
...

I'm trying to count the number of cells meeting certain criteria, but by
applying multiple criteria to more than one column of cells. Basically,
this would be the equivalent of row counting.

So, assuming I have Column A having letters "A", "B", "C", etc., and
Column B having numbers 1, 2, 3, etc., I'd like to be able to count
unique combinations of values in the two columns.

# ColA ColB
1 A 1
2 A 1
3 A 2
4 A 2
5 B 1
6 B 2
7 C 1
8 D 1

Something like COUNTIF(A1:A8,"A"+B1:B8,1).
The result would be 2, as there are 2 rows with ColA="A", ColB=1.

Microsoft only shows examples of doing this with one range. I'm hoping
there's a relatively simple notation for combining ranges, without
having to dig into VBScript. Any ideas? Thanks!


--
gkline
------------------------------------------------------------------------
gkline's Profile:
http://www.excelforum.com/member.php...o&userid=16599
View this thread:
http://www.excelforum.com/showthread...hreadid=314634