Find value for each month based on date & criteria

[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

 

[Frank Kabel]

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

 

[Bob Phillips]

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)

 

[miker1999]

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

 

[Bob Phillips]

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)