Find value for each month based on date & criteria

  • Thread starter Thread starter miker1999
  • Start date Start date
M

miker1999

Hello,
I just learned a little in order to find how many job positions
filled for Month to Date, but I don't quite understand my formul
enough to determine how to use it find # of jobs opened for each month
Here is my formula:

=SUMPRODUCT((MONTH(J$3:J$1000)=MONTH(TODAY()))*(YEAR(J$3:J$1000)=YEAR(TODAY()))*(K$3:K$1000="Ne
Hire"))

Ok...I am trying to learn. I will create a column for Jan - Dec. Ho
do I modify the formula for each month?

Thanks
 
Hi
for January
you may use
=SUMPRODUCT((MONTH(J$3:J$1000)=1)*(YEAR(J$3:J$1000)=YEAR(TODAY()))*(K$3
:K$1000="New Hire"))

for February
=SUMPRODUCT((MONTH(J$3:J$1000)=2)*(YEAR(J$3:J$1000)=YEAR(TODAY()))*(K$3
:K$1000="New Hire"))

and so on
 
Mike,

Does this adaptation make it easier for your to work

=SUMPRODUCT((YEAR(J$3:J$1000)=2004)*(TEXT(J$3:J$1000,"mmm")="Jan")*(K$3:K$10
00="New Hire"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Ok, through dissecting and trial and error, I have figured it out. Th
SUMPRODUCT returns the value of MONTH, YEAR,etc... so, January = 1
February = 2...etc
 
No, MONTH returns the month, YEAR returns the year. These tests,
(MONTH(J$3:J$1000)=1 etc., return an array of bollean values. Multiplying
two arrays of bollean values coerces them into an ary of numbers (0 or 1)
which SUMPRODUCT adds up. That is why my example, which doesn't use MONTH
but uses TEXT, returns the correct result just like Frank's.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top