A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SUMPRODUCT with Date?



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 09:04 PM posted to microsoft.public.excel.misc
KC
external usenet poster
 
Posts: 215
Default 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!D1500=B2),--(MONTH(Hiring!T1:T500=3)))

A3=Cons
B2=GMT


  #2  
Old May 12th, 2010, 09:29 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default SUMPRODUCT with Date?

Try this...

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

Where n = the quarter number 1 to 4.

--
Biff
Microsoft Excel MVP


"KC" wrote in message
...
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!D1500=B2),--(MONTH(Hiring!T1:T500=3)))

A3=Cons
B2=GMT




  #3  
Old May 12th, 2010, 10:08 PM posted to microsoft.public.excel.misc
Martin Fishlock
external usenet poster
 
Posts: 428
Default SUMPRODUCT with Date?

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!D1500=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!D1500=B2),--(MONTH(Hiring!T1:T500=3)))

A3=Cons
B2=GMT


  #4  
Old May 12th, 2010, 10:13 PM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 1,896
Default SUMPRODUCT with Date?

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!D1500=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!D1500=B2),--(MONTH(Hiring!T1:T500=3)))

A3=Cons
B2=GMT


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.