SUMPRODUCT with Date?

K

KC

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
 
T

T. Valko

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.
 
M

Martin Fishlock

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))
 
J

JLatham

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top