Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif, countif, datedif formula
K3:K1408 contain dates of hire i.e. 4/30/2007
would like to count how many were hired in year 2007, 1999, etc.. how what would be the formula, is it a nested formula? at a loss. really do not want to make a separate column just to provide the year then do a countif column. thanks for help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif, countif, datedif formula
The below will return the number of entries of year 2007.
=SUMPRODUCT(--(YEAR(K3:K1408)=2007)) If this post helps click Yes --------------- Jacob Skaria "v1rt8" wrote: K3:K1408 contain dates of hire i.e. 4/30/2007 would like to count how many were hired in year 2007, 1999, etc.. how what would be the formula, is it a nested formula? at a loss. really do not want to make a separate column just to provide the year then do a countif column. thanks for help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif, countif, datedif formula
On Sun, 24 May 2009 10:21:01 -0700, v1rt8
wrote: K3:K1408 contain dates of hire i.e. 4/30/2007 would like to count how many were hired in year 2007, 1999, etc.. how what would be the formula, is it a nested formula? at a loss. really do not want to make a separate column just to provide the year then do a countif column. thanks for help You can use COUNTIF also: For 2007, something like: =COUNTIF(K3:K1408,"="&DATE(2007,1,1)) - COUNTIF(K3:K1408,""&DATE(2007,12,31)) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif, countif, datedif formula
Hi,
If you are looking for a non-formula approach, you can use pivot tables a well. Just pivot the data and drag th dates column in the row and data area. Now Group the dates in the row area by years and you can now see the count of hires by year -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "v1rt8" wrote in message ... K3:K1408 contain dates of hire i.e. 4/30/2007 would like to count how many were hired in year 2007, 1999, etc.. how what would be the formula, is it a nested formula? at a loss. really do not want to make a separate column just to provide the year then do a countif column. thanks for help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif, SumProduct, CountIf Formula Help | Excel Discussion (Misc queries) | |||
CountIf, SumIf formula ques. | Excel Worksheet Functions | |||
Help - I need to combine both a SUMIF and COUNTIF into one formula | Excel Worksheet Functions | |||
Countif, Match or Sumif Formula | Excel Worksheet Functions | |||
Which formula to use? countif, sumif, sumproduct | Excel Discussion (Misc queries) |