| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Function
I have a 2500+ line document with different years indicated in column D. How
would I use the count (??) function to count the number of occurances for 1998, 1999, 2000, etc? The help function isn't very much help to me. Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Function
=COUNTIF(D1:D2500=1999) - use the year you want Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=504479 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Function
Type
=COUNTIF(D1:D2500,1999) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "EdMac" wrote in message ... =COUNTIF(D1:D2500=1999) - use the year you want Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=504479 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Function
*sigh*
I regretted to provide information that the dates are formatted as dd/mm/yyyy, so the formulas provided to me will not work. Is there another way this can be done? Sorry for the confusion. Thank you. "Bob Phillips" wrote: Type =COUNTIF(D1:D2500,1999) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "EdMac" wrote in message ... =COUNTIF(D1:D2500=1999) - use the year you want Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=504479 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Function
Try:
=SUMPRODUCT(--(YEAR(D1:D2500) = 1999)) -- Kevin Vaughn "MAB" wrote: *sigh* I regretted to provide information that the dates are formatted as dd/mm/yyyy, so the formulas provided to me will not work. Is there another way this can be done? Sorry for the confusion. Thank you. "Bob Phillips" wrote: Type =COUNTIF(D1:D2500,1999) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "EdMac" wrote in message ... =COUNTIF(D1:D2500=1999) - use the year you want Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=504479 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Function
This gives me a #VALUE! error message.
Is there anything else I can try? Thank you, MAB "Kevin Vaughn" wrote: Try: =SUMPRODUCT(--(YEAR(D1:D2500) = 1999)) -- Kevin Vaughn "MAB" wrote: *sigh* I regretted to provide information that the dates are formatted as dd/mm/yyyy, so the formulas provided to me will not work. Is there another way this can be done? Sorry for the confusion. Thank you. "Bob Phillips" wrote: Type =COUNTIF(D1:D2500,1999) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "EdMac" wrote in message ... =COUNTIF(D1:D2500=1999) - use the year you want Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=504479 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Function
What is in that range other than dates? I tried deleting a date to see if it
was being caused by blanks. That did not do anything. But when I inserted a space in a cell I got the #value error. I am sure that other data could cause that error. Like text (you can't perform the year function on text, only on dates.) -- Kevin Vaughn "MAB" wrote: This gives me a #VALUE! error message. Is there anything else I can try? Thank you, MAB "Kevin Vaughn" wrote: Try: =SUMPRODUCT(--(YEAR(D1:D2500) = 1999)) -- Kevin Vaughn "MAB" wrote: *sigh* I regretted to provide information that the dates are formatted as dd/mm/yyyy, so the formulas provided to me will not work. Is there another way this can be done? Sorry for the confusion. Thank you. "Bob Phillips" wrote: Type =COUNTIF(D1:D2500,1999) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "EdMac" wrote in message ... =COUNTIF(D1:D2500=1999) - use the year you want Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=504479 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Function
Hmm, I don't know. I just formatted all the cells where the dates are to
Date format (mm/dd/yyyy). I even did a text to columns on that column, yet I still get the #Value! error. Any other thoughts? Thanks for all your help so far. :-) "Kevin Vaughn" wrote: What is in that range other than dates? I tried deleting a date to see if it was being caused by blanks. That did not do anything. But when I inserted a space in a cell I got the #value error. I am sure that other data could cause that error. Like text (you can't perform the year function on text, only on dates.) -- Kevin Vaughn "MAB" wrote: This gives me a #VALUE! error message. Is there anything else I can try? Thank you, MAB "Kevin Vaughn" wrote: Try: =SUMPRODUCT(--(YEAR(D1:D2500) = 1999)) -- Kevin Vaughn "MAB" wrote: *sigh* I regretted to provide information that the dates are formatted as dd/mm/yyyy, so the formulas provided to me will not work. Is there another way this can be done? Sorry for the confusion. Thank you. "Bob Phillips" wrote: Type =COUNTIF(D1:D2500,1999) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "EdMac" wrote in message ... =COUNTIF(D1:D2500=1999) - use the year you want Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=504479 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Function
Ok, but what else is in that range? For instance D1 might be a heading.
Also, does it work if you do it on a small range? For instance, =SUMPRODUCT(--(YEAR(D3:D210) = 1999)) If it works on the smaller range, you could try it on progressively larger ranges until you get an error and thus locate the source of the problem (assuming the formula works for you at all. It is working for me, of course.) -- Kevin Vaughn "MAB" wrote: Hmm, I don't know. I just formatted all the cells where the dates are to Date format (mm/dd/yyyy). I even did a text to columns on that column, yet I still get the #Value! error. Any other thoughts? Thanks for all your help so far. :-) "Kevin Vaughn" wrote: What is in that range other than dates? I tried deleting a date to see if it was being caused by blanks. That did not do anything. But when I inserted a space in a cell I got the #value error. I am sure that other data could cause that error. Like text (you can't perform the year function on text, only on dates.) -- Kevin Vaughn "MAB" wrote: This gives me a #VALUE! error message. Is there anything else I can try? Thank you, MAB "Kevin Vaughn" wrote: Try: =SUMPRODUCT(--(YEAR(D1:D2500) = 1999)) -- Kevin Vaughn "MAB" wrote: *sigh* I regretted to provide information that the dates are formatted as dd/mm/yyyy, so the formulas provided to me will not work. Is there another way this can be done? Sorry for the confusion. Thank you. "Bob Phillips" wrote: Type =COUNTIF(D1:D2500,1999) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "EdMac" wrote in message ... =COUNTIF(D1:D2500=1999) - use the year you want Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=504479 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Function
LOL!
See, that's why I come here for help. I was including all of column D in the formula instead of just including the area where the valid data is. I got it to work when I used it correctly. Thanks for all your input and patience. MAB "Kevin Vaughn" wrote: Ok, but what else is in that range? For instance D1 might be a heading. Also, does it work if you do it on a small range? For instance, =SUMPRODUCT(--(YEAR(D3:D210) = 1999)) If it works on the smaller range, you could try it on progressively larger ranges until you get an error and thus locate the source of the problem (assuming the formula works for you at all. It is working for me, of course.) -- Kevin Vaughn "MAB" wrote: Hmm, I don't know. I just formatted all the cells where the dates are to Date format (mm/dd/yyyy). I even did a text to columns on that column, yet I still get the #Value! error. Any other thoughts? Thanks for all your help so far. :-) "Kevin Vaughn" wrote: What is in that range other than dates? I tried deleting a date to see if it was being caused by blanks. That did not do anything. But when I inserted a space in a cell I got the #value error. I am sure that other data could cause that error. Like text (you can't perform the year function on text, only on dates.) -- Kevin Vaughn "MAB" wrote: This gives me a #VALUE! error message. Is there anything else I can try? Thank you, MAB "Kevin Vaughn" wrote: Try: =SUMPRODUCT(--(YEAR(D1:D2500) = 1999)) -- Kevin Vaughn "MAB" wrote: *sigh* I regretted to provide information that the dates are formatted as dd/mm/yyyy, so the formulas provided to me will not work. Is there another way this can be done? Sorry for the confusion. Thank you. "Bob Phillips" wrote: Type =COUNTIF(D1:D2500,1999) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "EdMac" wrote in message ... =COUNTIF(D1:D2500=1999) - use the year you want Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=504479 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting Function
Glad you got it working.
-- Kevin Vaughn "MAB" wrote: LOL! See, that's why I come here for help. I was including all of column D in the formula instead of just including the area where the valid data is. I got it to work when I used it correctly. Thanks for all your input and patience. MAB "Kevin Vaughn" wrote: Ok, but what else is in that range? For instance D1 might be a heading. Also, does it work if you do it on a small range? For instance, =SUMPRODUCT(--(YEAR(D3:D210) = 1999)) If it works on the smaller range, you could try it on progressively larger ranges until you get an error and thus locate the source of the problem (assuming the formula works for you at all. It is working for me, of course.) -- Kevin Vaughn "MAB" wrote: Hmm, I don't know. I just formatted all the cells where the dates are to Date format (mm/dd/yyyy). I even did a text to columns on that column, yet I still get the #Value! error. Any other thoughts? Thanks for all your help so far. :-) "Kevin Vaughn" wrote: What is in that range other than dates? I tried deleting a date to see if it was being caused by blanks. That did not do anything. But when I inserted a space in a cell I got the #value error. I am sure that other data could cause that error. Like text (you can't perform the year function on text, only on dates.) -- Kevin Vaughn "MAB" wrote: This gives me a #VALUE! error message. Is there anything else I can try? Thank you, MAB "Kevin Vaughn" wrote: Try: =SUMPRODUCT(--(YEAR(D1:D2500) = 1999)) -- Kevin Vaughn "MAB" wrote: *sigh* I regretted to provide information that the dates are formatted as dd/mm/yyyy, so the formulas provided to me will not work. Is there another way this can be done? Sorry for the confusion. Thank you. "Bob Phillips" wrote: Type =COUNTIF(D1:D2500,1999) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "EdMac" wrote in message ... =COUNTIF(D1:D2500=1999) - use the year you want Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=504479 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| UDF help please | Excel Discussion (Misc queries) | |||
| Date & Time | New Users to Excel | |||
| Conversion | Excel Worksheet Functions | |||
| Formula to list unique values | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Linear Mode
