Home 
Search 
Today's 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




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




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




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




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




How do I use nested countif statements? Same ?
=sumproduct((B$2:B$474365),(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




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$474365),(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




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




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$474365),(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




How do I use nested countif statements? Same ?
The first had  instead of =
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$474365),(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 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Returning text from nested IF and Vlookup statements  Excel Worksheet Functions  
CONDITIONAL / NESTED COUNTIF  Excel Worksheet Functions  
UDFunctions and nested Iftheelse statements  Excel Worksheet Functions  
Countif Function Nested  Excel Discussion (Misc queries)  
Nested IF statements  Excel Worksheet Functions 