Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
gkline
 
Posts: n/a
Default 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

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=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



  #3   Report Post  
JulieD
 
Posts: n/a
Default

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I Need to divide all cells in rows 2 and 3 by 100 Brent E Excel Discussion (Misc queries) 3 December 24th 04 12:27 AM
Counting rows based on criteria in multiple cells Margaret Excel Discussion (Misc queries) 11 December 3rd 04 12:04 AM
Excel - if cells = 0, how to conditionally hide rows in chart fineimage Excel Worksheet Functions 1 November 11th 04 10:45 PM
Pivot table, how do you exclude counting cells with formulas as a Greg Bobak Excel Worksheet Functions 4 November 4th 04 01:05 AM
Count rows based on multiple criteria Murph Excel Worksheet Functions 1 October 28th 04 07:13 AM


All times are GMT +1. The time now is 01:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"