Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
NM NM is offline
external usenet poster
 
Posts: 51
Default Count with criteria

Hi,

I have a column B which can have input as 1, 0,a or sh. Another Column C can
have input as I,D,X,M. I want to count the number of "I"s in column C which
have "1" in column B. In other words if column B has o ans column c has I , I
do not want to count it.

Column B Column C
1 I
0 I
sh D

Thanks for your help.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default Count with criteria

A1: 1
A2: I
=SUMPRODUCT(--($B$2:$B$100=A1),--($C$2:C$100=A2))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"NM" wrote:

Hi,

I have a column B which can have input as 1, 0,a or sh. Another Column C can
have input as I,D,X,M. I want to count the number of "I"s in column C which
have "1" in column B. In other words if column B has o ans column c has I , I
do not want to count it.

Column B Column C
1 I
0 I
sh D

Thanks for your help.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default Count with criteria

Try this in A1 (or anywhere other than B1:C100)

=SUMPRODUCT(--(B1:B100=1),--(C1:C100="I"))

Adjust 100 to the last row in your data set.


"NM" wrote:

Hi,

I have a column B which can have input as 1, 0,a or sh. Another Column C can
have input as I,D,X,M. I want to count the number of "I"s in column C which
have "1" in column B. In other words if column B has o ans column c has I , I
do not want to count it.

Column B Column C
1 I
0 I
sh D

Thanks for your help.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Count with criteria

Try this

=SUMPRODUCT((B1:B30=1)*(C1:C30="I"))

Mike

"NM" wrote:

Hi,

I have a column B which can have input as 1, 0,a or sh. Another Column C can
have input as I,D,X,M. I want to count the number of "I"s in column C which
have "1" in column B. In other words if column B has o ans column c has I , I
do not want to count it.

Column B Column C
1 I
0 I
sh D

Thanks for your help.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Count with criteria

Try this:

=SUMIF(C:C,"I",B:B)

Hope this helps.

Pete

On Nov 12, 4:21*pm, NM wrote:
Hi,

I have a column B which can have input as 1, 0,a or sh. Another Column C can
have input as I,D,X,M. I want to count the number of "I"s in column C which
have "1" in column B. In other words if column B has o ans column c has I , I
do not want to count it.

Column B * * Column C
1 * * * * * * * * I * *
0 * * * * * * * * I *
sh * * * * * * * D

Thanks for your help.




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
NM NM is offline
external usenet poster
 
Posts: 51
Default Count with criteria

Thanks much! It works!

"Sheeloo" wrote:

Try this in A1 (or anywhere other than B1:C100)

=SUMPRODUCT(--(B1:B100=1),--(C1:C100="I"))

Adjust 100 to the last row in your data set.


"NM" wrote:

Hi,

I have a column B which can have input as 1, 0,a or sh. Another Column C can
have input as I,D,X,M. I want to count the number of "I"s in column C which
have "1" in column B. In other words if column B has o ans column c has I , I
do not want to count it.

Column B Column C
1 I
0 I
sh D

Thanks for your help.

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
Multiple Criteria, Count If, Sum Product to get count across range Jonathan Excel Worksheet Functions 5 January 10th 08 12:32 AM
count using criteria ferde Excel Discussion (Misc queries) 4 March 21st 07 05:01 PM
How to count nos. on 3 criteria Excel_Learner Excel Worksheet Functions 5 August 28th 06 03:17 PM
Count If 3 Criteria Met kieffer Excel Worksheet Functions 9 April 15th 06 12:38 AM
Sum Count of Criteria Every 3rd Row Sam via OfficeKB.com Excel Worksheet Functions 5 December 11th 05 03:21 AM


All times are GMT +1. The time now is 02:46 AM.

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"