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.

 How do I use nested countif statements?
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## How do I use nested countif statements?

#1
November 13th 06, 04:55 PM posted to microsoft.public.excel.worksheet.functions
 Frank P Florida external usenet poster Posts: 1
How do I use nested countif statements?

I want to be able to count the values in a list that meet two different
criteria. There is one column of dates and then another column of model
numbers. I want to create a row of the number of model number per date. Any
help is appreciated

#2
November 13th 06, 05:08 PM posted to microsoft.public.excel.worksheet.functions
 Roger Govier external usenet poster Posts: 2,886
How do I use nested countif statements?

Hi Frank

Try Sumproduct
=SUMPRODUCT((\$A\$1:\$A\$100=DATE(2006,11,13))*(\$B\$1:\$ B\$100="Model"))
Change the date value and "Model" to suit.

You may be looking for lust the Year, as opposed to an individual date,
in which case I would do the following.

Place your "Model" in cells e.g. in cell D210
and place your Dates in cells E1:H1 e.g 1/1/2000, 1/1/2001, 1/1/2002
then copy the following to E2 and copy down and across as appropriate
=SUMPRODUCT((\$A\$1:\$A\$100=E\$1))*(\$B\$1:\$B\$100=\$D2))

Better still, create a Pivot Table.
For help on creating Pivot Tables take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

--
Regards

Roger Govier

"Frank P Florida" <Frank P > wrote in
message ...
>I want to be able to count the values in a list that meet two different
> criteria. There is one column of dates and then another column of
> model
> numbers. I want to create a row of the number of model number per
> date. Any
> help is appreciated
>

#3
November 13th 06, 05:13 PM posted to microsoft.public.excel.worksheet.functions
 Dave F external usenet poster Posts: 2,574
How do I use nested countif statements?

You can't use nested countifs. You can, however, use SUMPRODUCT for that
purpose. See here for some info: http://www.meadinkent.co.uk/xlsumproduct.htm

Dave

--
Brevity is the soul of wit.

"Frank P Florida" wrote:

> I want to be able to count the values in a list that meet two different
> criteria. There is one column of dates and then another column of model
> numbers. I want to create a row of the number of model number per date. Any
> help is appreciated
>

#4
November 13th 06, 05:16 PM posted to microsoft.public.excel.worksheet.functions
 Allllen external usenet poster Posts: 341
How do I use nested countif statements?

use this if you are doing it in a cell

=SUMPRODUCT(--(a1:a50="big"),--(b1:b50="fat"),--(c1:c50="ugly"))

it will tell you how many records there are which meet the criteria for big
and fat and ugly.

otherwise you might look at pivot tables as a neat way of viewing your data.
Drag the date field into the columns, and the model number (count of model
number) into the data section.
very quick pivot table training is he
http://www.datapigtechnologies.com/ExcelMain.htm

--
Allllen

"Frank P Florida" wrote:

> I want to be able to count the values in a list that meet two different
> criteria. There is one column of dates and then another column of model
> numbers. I want to create a row of the number of model number per date. Any
> help is appreciated
>

#5
November 13th 06, 05:18 PM posted to microsoft.public.excel.worksheet.functions
 41db14 external usenet poster Posts: 10
How do I use nested countif statements? Same ?

Frank I also have the same question...
This is the CountIF function I'm using but can't seem to get it to do what I
want.
In column B I'm counting the number of days between dates that are equal to
or greater than 365 days...that part works fine...but I also want to have the
countif only look at a specific persons name in column "W". Excel accepts
the format but does not return a correct answer...??

=COUNTIF(B\$2:B\$474,">=365""Sam Jones")

"Frank P Florida" wrote:

> I want to be able to count the values in a list that meet two different
> criteria. There is one column of dates and then another column of model
> numbers. I want to create a row of the number of model number per date. Any
> help is appreciated
>

#6
November 13th 06, 05:28 PM posted to microsoft.public.excel.worksheet.functions
 Allllen external usenet poster Posts: 341
How do I use nested countif statements? Same ?

=sumproduct(--(B\$2:B\$474>-365),--(W\$2:W\$474="Sam Jones"))
--
Allllen

"41db14" wrote:

> Frank I also have the same question...
> This is the CountIF function I'm using but can't seem to get it to do what I
> want.
> In column B I'm counting the number of days between dates that are equal to
> or greater than 365 days...that part works fine...but I also want to have the
> countif only look at a specific persons name in column "W". Excel accepts
> the format but does not return a correct answer...??
>
> =COUNTIF(B\$2:B\$474,">=365""Sam Jones")
>
> "Frank P Florida" wrote:
>
> > I want to be able to count the values in a list that meet two different
> > criteria. There is one column of dates and then another column of model
> > numbers. I want to create a row of the number of model number per date. Any
> > help is appreciated
> >

#7
November 13th 06, 05:34 PM posted to microsoft.public.excel.worksheet.functions
 Allllen external usenet poster Posts: 341
How do I use nested countif statements? Same ?

sorry I should have written
=sumproduct(--(B\$2:B\$474>=365),--(W\$2:W\$474="Sam Jones"))

--
Allllen

"Allllen" wrote:

> =sumproduct(--(B\$2:B\$474>-365),--(W\$2:W\$474="Sam Jones"))
> --
> Allllen
>
>
> "41db14" wrote:
>
> > Frank I also have the same question...
> > This is the CountIF function I'm using but can't seem to get it to do what I
> > want.
> > In column B I'm counting the number of days between dates that are equal to
> > or greater than 365 days...that part works fine...but I also want to have the
> > countif only look at a specific persons name in column "W". Excel accepts
> > the format but does not return a correct answer...??
> >
> > =COUNTIF(B\$2:B\$474,">=365""Sam Jones")
> >
> > "Frank P Florida" wrote:
> >
> > > I want to be able to count the values in a list that meet two different
> > > criteria. There is one column of dates and then another column of model
> > > numbers. I want to create a row of the number of model number per date. Any
> > > help is appreciated
> > >

#8
November 13th 06, 09:16 PM posted to microsoft.public.excel.worksheet.functions
 Frank P Florida external usenet poster Posts: 3
How do I use nested countif statements?

Thank you very much for your help this worked perfectly.

"Roger Govier" wrote:

> Hi Frank
>
> Try Sumproduct
> =SUMPRODUCT((\$A\$1:\$A\$100=DATE(2006,11,13))*(\$B\$1:\$ B\$100="Model"))
> Change the date value and "Model" to suit.
>
> You may be looking for lust the Year, as opposed to an individual date,
> in which case I would do the following.
>
> Place your "Model" in cells e.g. in cell D210
> and place your Dates in cells E1:H1 e.g 1/1/2000, 1/1/2001, 1/1/2002
> then copy the following to E2 and copy down and across as appropriate
> =SUMPRODUCT((\$A\$1:\$A\$100=E\$1))*(\$B\$1:\$B\$100=\$D2))
>
> Better still, create a Pivot Table.
> For help on creating Pivot Tables take a look at the following sites
> http://peltiertech.com/Excel/Pivots/pivotstart.htm
> http://www.contextures.com/xlPivot02.html
>
> http://www.datapigtechnologies.com/f...es/pivot1.html
>
>
> --
> Regards
>
> Roger Govier
>
>
> "Frank P Florida" <Frank P > wrote in
> message ...
> >I want to be able to count the values in a list that meet two different
> > criteria. There is one column of dates and then another column of
> > model
> > numbers. I want to create a row of the number of model number per
> > date. Any
> > help is appreciated
> >

>
>
>

#9
November 13th 06, 09:58 PM posted to microsoft.public.excel.worksheet.functions
 41db14 external usenet poster Posts: 10
How do I use nested countif statements? Same ?

Allllen, Your suggestion worked great!! Thank you very much
What are "--" ?? I have never used them.
Could not tell the difference between your first suggested fomula and the
second?

"Allllen" wrote:

> sorry I should have written
> =sumproduct(--(B\$2:B\$474>=365),--(W\$2:W\$474="Sam Jones"))
>
> --
> Allllen
>
>
> "Allllen" wrote:
>
> > =sumproduct(--(B\$2:B\$474>-365),--(W\$2:W\$474="Sam Jones"))
> > --
> > Allllen
> >
> >
> > "41db14" wrote:
> >
> > > Frank I also have the same question...
> > > This is the CountIF function I'm using but can't seem to get it to do what I
> > > want.
> > > In column B I'm counting the number of days between dates that are equal to
> > > or greater than 365 days...that part works fine...but I also want to have the
> > > countif only look at a specific persons name in column "W". Excel accepts
> > > the format but does not return a correct answer...??
> > >
> > > =COUNTIF(B\$2:B\$474,">=365""Sam Jones")
> > >
> > > "Frank P Florida" wrote:
> > >
> > > > I want to be able to count the values in a list that meet two different
> > > > criteria. There is one column of dates and then another column of model
> > > > numbers. I want to create a row of the number of model number per date. Any
> > > > help is appreciated
> > > >

#10
November 14th 06, 03:05 AM posted to microsoft.public.excel.worksheet.functions
 JMB external usenet poster Posts: 2,062
How do I use nested countif statements? Same ?

The double unary operator is covered here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"41db14" wrote:

> Allllen, Your suggestion worked great!! Thank you very much
> What are "--" ?? I have never used them.
> Could not tell the difference between your first suggested fomula and the
> second?
>
>
> "Allllen" wrote:
>
> > sorry I should have written
> > =sumproduct(--(B\$2:B\$474>=365),--(W\$2:W\$474="Sam Jones"))
> >
> > --
> > Allllen
> >
> >
> > "Allllen" wrote:
> >
> > > =sumproduct(--(B\$2:B\$474>-365),--(W\$2:W\$474="Sam Jones"))
> > > --
> > > Allllen
> > >
> > >
> > > "41db14" wrote:
> > >
> > > > Frank I also have the same question...
> > > > This is the CountIF function I'm using but can't seem to get it to do what I
> > > > want.
> > > > In column B I'm counting the number of days between dates that are equal to
> > > > or greater than 365 days...that part works fine...but I also want to have the
> > > > countif only look at a specific persons name in column "W". Excel accepts
> > > > the format but does not return a correct answer...??
> > > >
> > > > =COUNTIF(B\$2:B\$474,">=365""Sam Jones")
> > > >
> > > > "Frank P Florida" wrote:
> > > >
> > > > > I want to be able to count the values in a list that meet two different
> > > > > criteria. There is one column of dates and then another column of model
> > > > > numbers. I want to create a row of the number of model number per date. Any
> > > > > help is appreciated
> > > > >

 Thread Tools Display Modes Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Returning text from nested IF and Vlookup statements Patricia Excel Worksheet Functions 10 July 28th 06 04:22 PM CONDITIONAL / NESTED COUNTIF OrlandoFreeman Excel Worksheet Functions 3 July 2nd 06 01:07 AM UDFunctions and nested If-the-else statements JDB Excel Worksheet Functions 1 January 25th 06 03:29 PM Countif Function -Nested Angi Excel Discussion (Misc queries) 7 May 4th 05 07:04 PM Nested IF statements John Simons Excel Worksheet Functions 14 February 16th 05 06:17 AM

All times are GMT +1. The time now is 11:38 PM.