Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
onthefritz
 
Posts: n/a
Default Count number of items in one column that have a value in another?

I have a spreadsheet that I would like to count the number of occurrences of
a particular value in one column that also have a particular value in another.

For instance, I want the number of times the value in column B = Yes when
the value in column C = 0.

I've tried using COUNTIF and SUMIF, but they both give me the result of the
two arguments added together as opposed to how many contain both values.

I would certainly appreciate any ideas.

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Chris Lavender
 
Posts: n/a
Default Count number of items in one column that have a value in another?

Add a formula in Col D (or wherever) that concatenates B and C (eg =B1&C1),
then do a COUNTIF on Col D (eg =COUNTIF(D:D,"YES0")

HTH Best rgds
Chris Lav

"onthefritz" wrote in message
...
I have a spreadsheet that I would like to count the number of occurrences

of
a particular value in one column that also have a particular value in

another.

For instance, I want the number of times the value in column B = Yes when
the value in column C = 0.

I've tried using COUNTIF and SUMIF, but they both give me the result of

the
two arguments added together as opposed to how many contain both values.

I would certainly appreciate any ideas.

Thanks.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Chris Lavender
 
Posts: n/a
Default Count number of items in one column that have a value in another?

Add a formula in Col D (or wherever) that concatenates B and C (eg =B1&C1),
then do a COUNTIF on Col D (eg =COUNTIF(D:D,"YES0")

HTH Best rgds
Chris Lav

"onthefritz" wrote in message
...
I have a spreadsheet that I would like to count the number of occurrences

of
a particular value in one column that also have a particular value in

another.

For instance, I want the number of times the value in column B = Yes when
the value in column C = 0.

I've tried using COUNTIF and SUMIF, but they both give me the result of

the
two arguments added together as opposed to how many contain both values.

I would certainly appreciate any ideas.

Thanks.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Count number of items in one column that have a value in another?

Hi!

Try this:

=SUMPRODUCT(--(B1:B20="yes"),--(ISNUMBER(C1:C20)),--(C1:C20=0))

Biff

"onthefritz" wrote in message
...
I have a spreadsheet that I would like to count the number of occurrences
of
a particular value in one column that also have a particular value in
another.

For instance, I want the number of times the value in column B = Yes when
the value in column C = 0.

I've tried using COUNTIF and SUMIF, but they both give me the result of
the
two arguments added together as opposed to how many contain both values.

I would certainly appreciate any ideas.

Thanks.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Count number of items in one column that have a value in another?

onthefritz wrote...
I have a spreadsheet that I would like to count the number of occurrences of
a particular value in one column that also have a particular value in another.

For instance, I want the number of times the value in column B = Yes when
the value in column C = 0.

....

No need to use additional supporting cells/ranges. Try something like

=SUMPRODUCT(--(B2:B1001="Yes"),--(C2:C1001=0))



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
onthefritz
 
Posts: n/a
Default Count number of items in one column that have a value in another?



"onthefritz" wrote:

I have a spreadsheet that I would like to count the number of occurrences of
a particular value in one column that also have a particular value in another.

For instance, I want the number of times the value in column B = Yes when
the value in column C = 0.

I've tried using COUNTIF and SUMIF, but they both give me the result of the
two arguments added together as opposed to how many contain both values.

I would certainly appreciate any ideas.

Thanks.


Chris Lav -

Thanks for the help. This worked great!

Jeff
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
match and count words David Excel Worksheet Functions 5 July 4th 05 02:24 AM
Count cells based on date range in another column [email protected] New Users to Excel 1 May 5th 05 08:11 PM
count number of items in a row? help *file included* Carlos Excel Worksheet Functions 7 April 29th 05 01:17 PM
Count number of unique items in a column that contains duplicates Steembeem Excel Worksheet Functions 3 February 2nd 05 01:51 AM
How can I sort an entire spreadsheet from a list prod sorter Excel Worksheet Functions 4 November 17th 04 04:43 AM


All times are GMT +1. The time now is 02:22 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"