SUMPRODUCT with Date?

I'm trying to use the formula below, but its not working correctly.  On the 
Hiring tab, column T has a date formatted as MM/DD/YYYY.  I want a formula 
that counts the number of hires in a quarter with other criteria.  So for 
instance  if column U = A3 (Cons) and column D = GMT and column T is in 1Q 
(Jan thru March).  I'll also need the same formula for 2Q (April thru June), 
3Q & 4Q.   

=SUMPRODUCT(--(Hiring!U1:U500=A3),--(Hiring!D1:D500=B2),--(MONTH(Hiring!T1:T500<=3)))

A3=Cons
B2=GMT


0
Utf
5/12/2010 8:04:00 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
816 Views

Similar Articles

[PageSpeed] 22

Try this...

=SUMPRODUCT(--(Hiring!U1:U500=A3),--(Hiring!D1:D500=B2),--(CEILING(MONTH(Hiring!T1:T500)/3,1)=n))

Where n = the quarter number 1 to 4.

-- 
Biff
Microsoft Excel MVP


"KC" <KC@discussions.microsoft.com> wrote in message 
news:10E9CB17-35CF-453F-9435-5EE214D3ED51@microsoft.com...
> I'm trying to use the formula below, but its not working correctly.  On 
> the
> Hiring tab, column T has a date formatted as MM/DD/YYYY.  I want a formula
> that counts the number of hires in a quarter with other criteria.  So for
> instance  if column U = A3 (Cons) and column D = GMT and column T is in 1Q
> (Jan thru March).  I'll also need the same formula for 2Q (April thru 
> June),
> 3Q & 4Q.
>
> =SUMPRODUCT(--(Hiring!U1:U500=A3),--(Hiring!D1:D500=B2),--(MONTH(Hiring!T1:T500<=3)))
>
> A3=Cons
> B2=GMT
>
> 


0
T
5/12/2010 8:29:34 PM
Hello:

I think that the following is incorrect:
--(MONTH(Hiring!T1:T500<=3)))

Try

--(MONTH(Hiring!T1:T500)<=3))

ie 

SUMPRODUCT(--(Hiring!U1:U500=A3),--(Hiring!D1:D500=B2),--(MONTH(Hiring!T1:T500)<=3))

-- 
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"KC" wrote:

> I'm trying to use the formula below, but its not working correctly.  On the 
> Hiring tab, column T has a date formatted as MM/DD/YYYY.  I want a formula 
> that counts the number of hires in a quarter with other criteria.  So for 
> instance  if column U = A3 (Cons) and column D = GMT and column T is in 1Q 
> (Jan thru March).  I'll also need the same formula for 2Q (April thru June), 
> 3Q & 4Q.   
> 
> =SUMPRODUCT(--(Hiring!U1:U500=A3),--(Hiring!D1:D500=B2),--(MONTH(Hiring!T1:T500<=3)))
> 
> A3=Cons
> B2=GMT
> 
> 
0
Utf
5/12/2010 9:08:04 PM
That was going to be my response, but the site went sloooooow on me.  

T.Valko's will give the quarter, which may aid him further down the road 
rather than having to add yet another --() to see if the month is between 3:7 
(2nd qtr), 6:10 (3rd qtr) or altering to >9 for 4th quarter.

"Martin Fishlock" wrote:

> Hello:
> 
> I think that the following is incorrect:
> --(MONTH(Hiring!T1:T500<=3)))
> 
> Try
> 
> --(MONTH(Hiring!T1:T500)<=3))
> 
> ie 
> 
> SUMPRODUCT(--(Hiring!U1:U500=A3),--(Hiring!D1:D500=B2),--(MONTH(Hiring!T1:T500)<=3))
> 
> -- 
> Hope this helps
> Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
> Please do not forget to rate this reply.
> 
> 
> "KC" wrote:
> 
> > I'm trying to use the formula below, but its not working correctly.  On the 
> > Hiring tab, column T has a date formatted as MM/DD/YYYY.  I want a formula 
> > that counts the number of hires in a quarter with other criteria.  So for 
> > instance  if column U = A3 (Cons) and column D = GMT and column T is in 1Q 
> > (Jan thru March).  I'll also need the same formula for 2Q (April thru June), 
> > 3Q & 4Q.   
> > 
> > =SUMPRODUCT(--(Hiring!U1:U500=A3),--(Hiring!D1:D500=B2),--(MONTH(Hiring!T1:T500<=3)))
> > 
> > A3=Cons
> > B2=GMT
> > 
> > 
0
Utf
5/12/2010 9:13:01 PM
Reply:

Similar Artilces:

how to do date 15/06/04 plus 5 months?
hi what formula should i use if i want to work out the date of say the 15/6/04 plus 5 months from now?? thanks : -- Message posted from http://www.ExcelForum.com the only way i know is to type this formula into the cell: =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) the cell "A1" (or any cell) will be the cell that the original date will be in. just add in the 6 after the MONTH (A1) to get your new date hope that helps >-----Original Message----- >hi what formula should i use if i want to work out the date of say > >the 15/6/04 plus 5 months from now?? ...

a status like "checking" to change to date format when completed
I am creating a DB that is for Tracking engineering drawings. I want to have a status field that has the following 1. not started 2. in process 3. in checking 4 complete / Date completed Is it possible to have the complete status change to a date somehow? I want to avoid having two fields, a status field and a complete field I would suggest that you may want to change that to have four date fields. DateReceived DateStarted DateReviewStarted DateComplete That way you can easily check its status in the process and get a complete timeline. but to answer your speci...

Date format #21
I can't see how to format the date in the UK manner, i.e. 6/12/09, rather than in the US manner 12/6/09. Can anyone help? Excel 2000. Rob Graham Select the range to format Format|Cells|Number tab Choose date and look to see if the day, month year is in the order you like. If it is, you can use that. If it's not, choose Custom and type: dd/mm/yyyy (or what you like) If the values don't change, then your dates aren't really dates. They're plain old text that look like dates. Select your range (one column at a time) data|text to columns Fixed width remove any lines t...

automatically format dates in a cell
Hi All, when I type a date into a cell, it automatically converts it to a date-time variable and formats it according to the system parameters. Is there any way I can turn this off? I don't want my date as a date time variable, nor do I want it to format to what the system format is. thanks stephan I'm not sure what a "date time variable" is, but if you want an XL date, but in the format you specify, preformat the cells with that format. If you don't want the date converted to an XL date at all, then preformat the cells as Text. They will then be left in whatev...

CSVDE Dates in Excel
How do I convert the Active Directory formatted dates exported by CSVDE when viewing in Excel to a readable format? http://groups.google.com/groups?hl=en&lr=&threadm=%23msiz%23Z8DHA.3804%40tk2msftngp13.phx.gbl&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26q%3Dldifde%2B%2522date%2Bformat%2522%26btnG%3DSearch You may have some luck with the dsquery tools by sending the output to a text file. I haven't checked to see if it converts for you or not though. Al "D-a-n_L" <djlajoie@hotmail.com> wrote in message news:%23Wxhlm6uEHA.2192@TK2MSFTNGP14.phx.gbl... >...

Sales Report filtered by time, Z Report filtered by date
Does anyone have any Z summary reports that can be filtered by day, week, etc Also does anyone have a sales summary report that can be filtered by date and time. (Our liquor store is open untill 1 AM and I can't get the broken out sales over the "two day" period once sales has started on the next day? Thank you so much in advance. There is a combined Z report on the Jean Holland web site (I believe) that does exactly what you want it to do. In regards to (sales) reports by date and time: I asked the same question a week or so ago in this NG but didn't get any soluti...

How do I hold email in Exchange Queue to be sent at specific time/date?
Our company is in the process of creating a weekly email newsletter/reminder to be sent out to subscribing customers. I'm using an account on our Exchange 2003 SP2 server to relay emails from our website to the customer. I want to be able to queue up and hold the emails for this account to be sent at a specific time, say Monday mornings at 8am, but I don't want to interfere with the normal operation of the rest of the organization's incoming/outgoing email. How do I go about doing this? I've looked at the SMTP Connectors and it appears as if this is possible, but I do...

Sorting dates, ignoring year
I have a speadsheet of birthdays which I would like to change to be birthdates. At the moment, everyone's birthdays are specified on one column in the format dd/mm/2002 so that I can sort the worksheet to keep the dtaes in order throughout the year. Is there an easy way to sort a column of dates but ignore the year? Or do I need to split the dates into multiple columns? -- Ian -- You could split the dates into separate columns: =month(a1) =day(a1) and =year(a1) (you wouldn't use the year column) But I'd insert a helper column with formulas like: ...

Finding the difference in days between two dates?
Hi, I was wondering if anyone could let me know how I can find the difference in days between two date fields, would appreciate the help. Thanks Damon Damon; Just try this. Enter two dates ( 01/08/2003 and 21/08/2003 ) In another cell subtract the two dates, but format that cell as "General". If all is well, just must find a 20 in that cell. Or is it too simple and not what you want ? Mark Rosenkrantz. More Excel ? www.rosenkrantz.nl or info@rosenkrantz.nl ----------------------------------------------------------------- "Damon" <damon@nospam.co.uk...

Dynamic Date Ranges
One one sheet I have a time series of data sorted by month. How do I go about (on a different sheet) using some sort of dropdown box (either through data validation or a combo box), so that the user can select a beginning date and end date, and the spreadsheet will display only the information in that time period. The number of columns would have to dynamically adjust I believe. Thanks! -- jc94321 ------------------------------------------------------------------------ jc94321's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27258 View this thread: http://ww...

Date sorting problem
Using Excel 2002 - we have huge list of dates that often have no DAY, or that only have a year. We have to sort them and don't know how. Such as: 12/31/02 12/00/02 2002 Thank you, Ardell You need to make some simple assumptions. For example, if there is no day, then assume the day=1. If there is no month and no day, then assume 1 jan. Then re-format the data. Then sort in the usual fashion -- Gary's Student "Ardell" wrote: > Using Excel 2002 - we have huge list of dates that often have no DAY, or > that only have a year. We have to sort them and don'...

Difference in years and months between 2 dates
Hi, Does anyone know how I can get the number of years and months between 2 dates? (Using Office 2000) Thanx Subtract one from the other "Els" <Els@discussions.microsoft.com> wrote in message news:E54C0D76-1313-40EF-922C-FCB0780AD992@microsoft.com... : Hi, : : Does anyone know how I can get the number of years and months between 2 : dates? (Using Office 2000) : : Thanx Hi see http://www.cpearson.com/excel/datedif.htm >-----Original Message----- >Hi, > >Does anyone know how I can get the number of years and months between 2 >dates? (Using Office 200...

auto date
Can someone tell me how to turn off the auto date part of excel? I need to insert different dates but everytime I try to input a date such as 6/02, 06/02, June 02, it always changes it to 6-05, 06-05, June-05. Any time I try any year after 2000 it does this. Thanks Luke ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- Hi Luke, You indicate what you entered not what it is supposed to mean, we don...

printing the saved date/time
is the a field code or something to print the save date in a footer i.e there is a field code for the current date in excel &[date], but i would like it to be the last date it was saved thanks in advance!!! You need VBA to do this If you copy this in the ThisWorkbook module it will print the Last Save Time in the RightFooter of every sheet you print Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ThisWorkbook.Worksheets wkSht.PageSetup.RightFooter = "&8Last Saved : " & _ ActiveWorkbook.BuiltinDoc...

how do I specify a date range = date(2010,4,1 thru 30)
I am trying to use a header in a formula. In A1 I have the month and year (April 2010). I want to bring in a calculation result from a second page (Daily Log) So I want to know how many guest in the month of April visited. But it has to look up April from the year list. I guess this is what I am trying to accomplish Lookup (A1) April (1-30) 2010 from April Stats sheet on Daily log sheet (A column) add guests logged in (D Column) and result to April Stats B5 I record numbers daily and want to have the totals from one page brought to another page, but need the data to be found...

perl Win32::OLE excel date
I need help retrieving the date from excel using the Win32::OLE pm. here is my program... use strict; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; # die on errors... my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); # get already active Excel # application or open new my $Book = $Excel->Workbooks->Open("C:\\TMP\\test1.xls"); # open Excel file my ...

Difficulty Aligning Series Data With Proper Dates
I ‘m building a column graph using Excel 2007 and I’m having difficulty aligning a series of data with the proper dates. I have 2 series of data – Series 1: Includes a value for each quarter in 2009 (e.g., Mar-09, Jun-09, etc). Series 2: Includes a value for each quarter in 2010 (e.g., Mar-10, Jun-10, etc). After creating each series, I select the appropriate dates (Series 1 – 2009 dates / Series 2 – 2010 dates). The first issue that I’m having is that when I select the second set of dates (for Series 2), it overrides the first – so the x-axis initially shows 2009 dates, but then ...

correct date/days in a template
I have found a template in M/S excel download that I would like to use but how do I change the dates. The current template is dated 11/1/2004 -11/14/2004 The template is a biweekly time card which has to be changed every 2 wks and additional days need to be added for when the the starting date starts. This is the link if you care to look at it to help understand what I am trying to explain http://office.microsoft.com/en-us/templates/TC060888761033.aspx?CategoryID=CT063469961033 Thank-you have a safe 4th hi, did you try to change the 11/1/2004 to 11/15/2004? if yes change the day ...

converting a number to a Julian Date
I am trying to take part of a claim number that is a Julian Date and translate it into a calendar date. Any suggestions on what to use in my query. Here are some examples of the 4 digit numbers: 9248 9230 9342 9345 Thank you - Lisa There are actually several different definitions of Julian Date. Which one are you trying to use? (In other words, to what should those four sample values equate?) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "LisaK" <LisaK@discussions.microsoft.com> wrote in message news:...

Tasks not following date change
I recently had a drive crash and had to save all my appointments and tasks from my back-up file to another laptop. The save went fine but now when I click on another date in the calendar box above the task pad display the calendar of appointments goes to the selected date but the tasks stay the same. To view scheduled tasks on another day I have to change the date in the laptop's clock settings to fool the task display into thinking it's that day. Previously, when I selected a different day, both the appointments AND the tasks changed. I am using Outlook 2000 and can't fi...

how do you compare two dates to find the difference?
mm/dd/yyyy is the format. I need to subtract two dates to find the difference Hi =A2-A1 and format the result as 'Number' -- Regards Frank Kabel Frankfurt, Germany Todd wrote: > mm/dd/yyyy is the format. I need to subtract two dates to find the > difference With dates in A1 & A2, in A3: =A1-A2 This will give you the number of days difference. Format A3 as general "Todd" <Todd@discussions.microsoft.com> wrote in message news:19B34084-E5BF-4D21-BB4E-DE60DB66CAE3@microsoft.com... > mm/dd/yyyy is the format. I need to subtract two dates to find the ...

Date field Update OnChange (or FireOnChange)
Hi I'm stuck on outo populating a custom date field in the Case form. The custom field indicates an SLA deadline date & time. I've used a code (see below) and it shows a simple SLA deadline time as an Alert, but does not set/populate the date & time for the field. I also need to make the SLA deadline dependant on 3 different dropdown/picklist fields on the same form (2 of these picklist forms also need to be auto populated on fire of the change of the first field - Case Type, the otehr two fields are Severity and Priority) The SLA deadline is dependant on the current time (o...

Automatic Time and date in email templates
How can i use automatic time and date in email templates? Automatic as in how and where? -----Original Message----- From: manuel.martinez@keyson.es [mailto:manuel.martinez@keyson.es] Posted At: Monday, November 28, 2005 11:41 PM Posted To: microsoft.public.crm Conversation: Automatic Time and date in email templates Subject: Automatic Time and date in email templates How can i use automatic time and date in email templates? ...

AP out of balance to G/L- AP trans with strange date
When reconciling AP to the GL this month I am out of balance by a transaction dated 9/30/1994 and it says it was paid on 8/22/2049. This transaction must have been entered years ago, no one currently here entered it. It is showing up on my AP aging but not the G/L. How do I get rid of it? I don't want a life ling reconciling item..... There is no distribution showing for either the invoice or payment. It has never appeared before - so I don't know what triggered it to suddenly show up. Any suggestions? On Feb 7, 6:43 pm, Jedwards <Jedwa...@discussions.microsoft.com> wrot...

Converting Dates between Gregorian to Afghani Calendar
Hello, I need your help in creating a calendar that converts the dates fro Gregorian Calendar to Afghani Calendar. Afghani Calendar is sola calendar which mean it has 365 days. It is not Lunar Calendar. So yea 2006 = 1385 afghani calendar. So this mean that G= A + 621 and A= G 621. The problem I have is converting the months and days. The new year fo Afghani Calendar is march 20 or 21 (depends on which day is the firs day of spring). So the new year 1/1/2006 is 3/20/1385. I would like to create a excel file that does this conversion and a well show the month and days graphically. :rol...