A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I count cells with different criteria in excel?



 
 
Thread Tools Display Modes
  #1  
Old January 5th 07, 04:49 PM posted to microsoft.public.excel.worksheet.functions
Richie17
external usenet poster
 
Posts: 3
Default How do I count cells with different criteria in excel?

I have a column with M or F (Male/Female) and I also have a column that has
their age. How do I count how many 6 year olds there are AND how many of
those are either male or female?
Ads
  #2  
Old January 5th 07, 04:51 PM posted to microsoft.public.excel.worksheet.functions
Dave F
external usenet poster
 
Posts: 2,574
Default How do I count cells with different criteria in excel?

You would need to use SUMPRODUCT.

Something like =SUMPRODUCT(--(A1:A100="M"),--(B1:B10=6))

Dave
--
Brevity is the soul of wit.


"Richie17" wrote:

> I have a column with M or F (Male/Female) and I also have a column that has
> their age. How do I count how many 6 year olds there are AND how many of
> those are either male or female?

  #3  
Old January 5th 07, 04:59 PM posted to microsoft.public.excel.worksheet.functions
Richie17
external usenet poster
 
Posts: 3
Default How do I count cells with different criteria in excel?

Dave you are my new hero!...but now that you helped me with that does that
mean I have to get back to some real work?

My co-workers and myself appreciate what this can do for us now.
Thanks,
Richie17

"Dave F" wrote:

> You would need to use SUMPRODUCT.
>
> Something like =SUMPRODUCT(--(A1:A100="M"),--(B1:B10=6))
>
> Dave
> --
> Brevity is the soul of wit.
>
>
> "Richie17" wrote:
>
> > I have a column with M or F (Male/Female) and I also have a column that has
> > their age. How do I count how many 6 year olds there are AND how many of
> > those are either male or female?

  #4  
Old January 5th 07, 05:01 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,718
Default How do I count cells with different criteria in excel?

=SUM((A1:A100="M")*(B1:B100=6))

ctrl+shift+enter, not just enter


"Richie17" wrote:

> I have a column with M or F (Male/Female) and I also have a column that has
> their age. How do I count how many 6 year olds there are AND how many of
> those are either male or female?

  #5  
Old January 5th 07, 05:09 PM posted to microsoft.public.excel.worksheet.functions
Richie17
external usenet poster
 
Posts: 3
Default How do I count cells with different criteria in excel?

Thanks Teethless Mama!

Your suggestion also works. I'm not sure why but hey why complain!

Thanks,
Richie17

"Teethless mama" wrote:

> =SUM((A1:A100="M")*(B1:B100=6))
>
> ctrl+shift+enter, not just enter
>
>
> "Richie17" wrote:
>
> > I have a column with M or F (Male/Female) and I also have a column that has
> > their age. How do I count how many 6 year olds there are AND how many of
> > those are either male or female?

  #6  
Old January 5th 07, 06:16 PM posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
external usenet poster
 
Posts: 4,393
Default How do I count cells with different criteria in excel?

Read here from details
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes (do I get a Hero medal?)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Richie17" > wrote in message
...
> Dave you are my new hero!...but now that you helped me with that does that
> mean I have to get back to some real work?
>
> My co-workers and myself appreciate what this can do for us now.
> Thanks,
> Richie17
>
> "Dave F" wrote:
>
>> You would need to use SUMPRODUCT.
>>
>> Something like =SUMPRODUCT(--(A1:A100="M"),--(B1:B10=6))
>>
>> Dave
>> --
>> Brevity is the soul of wit.
>>
>>
>> "Richie17" wrote:
>>
>> > I have a column with M or F (Male/Female) and I also have a column that
>> > has
>> > their age. How do I count how many 6 year olds there are AND how many
>> > of
>> > those are either male or female?



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I count nonblank text cells in Excel gerns Excel Discussion (Misc queries) 3 November 7th 08 03:46 PM
Count Cells that meet Criteria kmason Excel Worksheet Functions 6 August 24th 06 04:31 PM
count criteria within a set range in excel Allan from Melbourne Excel Discussion (Misc queries) 3 August 1st 06 03:11 PM
How to count the number of Excel cells with text formatted Italic phausman Excel Worksheet Functions 3 July 19th 06 04:32 PM
Cells User Select Locked after upgrade to Excel 2002 TWilson Excel Discussion (Misc queries) 1 August 5th 05 12:22 PM


All times are GMT +1. The time now is 09:57 AM.


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