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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|