Excel - Countif and dates
Asked By To
14-Feb-08 11:21 AM
I need to count datess in a column if they occur in a specific month and year.
Can anyone help?
--
Tom
Excel 2007
(1)
SUMPRODUCT
(1)
Teethless
(1)
Gsnu200769
(1)
Datess
(1)
Month
(1)
Mama
(1)
YEAR
(1)
GarysStuden replied...
Put some dates in A1 thru A30:
11/13/2008
11/10/2007
9/2/2007
3/17/2008
5/1/2007
7/9/2008
1/7/2007
10/5/2008
8/27/2007
11/10/2007
6/5/2007
3/21/2007
3/27/2008
5/17/2007
5/2/2008
3/23/2007
10/18/2008
4/6/2008
2/14/2007
12/5/2008
7/3/2007
4/26/2007
9/21/2008
8/23/2008
8/25/2007
6/7/2007
3/3/2007
8/10/2008
8/12/2007
9/4/2008
then
=SUMPRODUCT(--(YEAR(A1:A30)=2007),--(MONTH(A1:A30)=5))
will give you the count for May 2007
--
Gary''s Student - gsnu200769
Teethlessmam replied...
=SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2008)
To replied...
Thanks to both of you.
--
Tom
Dave Peterson replied...
Another one:
=sumproduct(--(text(a1:a100,"yyyymm")="200804")
Adjust the ranges to match--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
--
Dave Peterson
Win7 VirtualStore Weirdness with Excel 2002 and earlier Excel I thought I'd share a weird experience I had today in case it saves installer for it - - which registers it as an add-in for all installed versions of Excel - - and tested it. Fine in Windows 2000 in all installed versions of Excel. Boot to Windows XP, fine there too. Boot to Windows 7. Fine in Excel 2010, 2007, and 2003. But Excel 2002 and 2000 were still loading the old version of the add-in. I uninstalled the add-in, made sure it was gone, and reinstalled it. Same problem in Excel 2002 and earlier; still works fine in Excel 2003 and later. I opened the VB
Calculate actual trunk utilization based on call start / duration Excel I am 'attempting' to calculate our phone line utilization at work. The end goal is the more difficult it becomes. I have roughly 250, 000 calls (I'm obviously using excel 2007) that I am attempting to run calculations against. Here is a sampling of the data a way to determine how many lines we actually need. Good Luck and Thanks, Nathaniel Excel Programming Discussions Sheets (1) Excel (1) GetMaxUsageBySecond (1) SUMPRODUCT (1) INDIRECT (1) Macro (1) SUM (1) MAX (1) Let's say the Start data the END data is in Column C1:C68. Put this in D1 and copy down. = SUMPRODUCT(- -($A$1:$A$68> = A1), - -($C$1:$C$68< = C1)) - - HTH, Barb Reinhardt Now that time increments of 1 minute or 10 minutes, whatever you want and use a similar SUMPRODUCT calculation. If you need help, let me know. - - HTH, Barb Reinhardt I haven't seen
Pb de recalcul Excel Bonjour, J'ai un problème de recalcul de ma feuille excel. J'importe une colonne qui se retrouve en texte 0000 0000 0000 0000 FFF0 7FFF cela soit automatique ? Merci d'avance à ceux qui se pencheront sur mon cas. Caroual Excel - French Discussions WorksheetFunction (1) AddIns (1) Worksheets (1) Excel 2007 (1) Macro (1) Proc (1) LEFT (1) VBA (1) Bonsoir Dans la doc sur HEXBIN 426a74cc@news.free.fr. . . Bonjour, J'ai un probl?me de recalcul de ma feuille excel. J'importe une colonne qui se retrouve en texte 0000 0000 0000 0000 FFF0 7FFF e ou Bonjour, D??butons par une petite explication : Auparavant sous les versions ant??rieures ?? Excel 2007, si les 2 macros compl??mentaires ("utilitaire d'analyse" ou titre d'exemple directement dans
INT defect: Please try this on 2007 for me Excel Would someone who has Excel 2007 running on an Intel-compatible computer (i.e. not a Mac) please try the examples below and post your results here? I posted this inquiry to an Excel 2010 blog. Not sure if / when I will hear back from them. (Extra credit: If you have Excel 2010 running on an Intel-compatible computer, feel to try these examples and post your results here, too.) Has the following Excel 2003 problem been fixed in 2010 (or 2007)? INT(123456789 - 0.0000004) returns 123456789 instead of 123456788. This causes a problem in formulas
32 bit DLL with 64 bit Excel 2010 Excel We have an app which creates large Excel workbooks. For example, one workbook has 1, 000 worksheets. In other cases there are fewer worksheets but the Excel file can be 80 MB or larger. Since this sometimes crashes Excel 2007 and 2003, I am considering using 64 bit Excel 2010. Would 64-bit Excel be more reliable with workbooks this size than 32-bit Excel? Is 32-bit Excel