It still gives me #VALUE! as the result. Sorry.

"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH($H$2:$S$2)<=MONTH(C5)),H5:S5)


"Office User" wrote:

I'm trying to figure year to date totals based on month. The formula needs
to figure a person's total hours of vacation used since anniversary month
(which is just the month of their hire date). I have a spreadsheet with
month headings Jan thru Dec in H2:S2 with vacation hours taken each month by
each employee in rows under these headings. It looks something like this:

Jan Feb Mar . . .
10 0 10
0 8 0
2 0 0

If the first person started working in March, their total YTD needs to just
add months March through Dec, but the next person may have started in Sept so
that line needs to add months Sept through Dec.

With hire date in column C, month headings in H2:S2, and data in row 5,
here's the formula I have tried but receive #VALUE!

=SUMPRODUCT(--(--(MONTH($H$2:$S$2))<=(MONTH(C5))),--(--(H5:S5)))

Any help will be greatly appreciated. Thanks,
Marcia

Actually the formula works if I change my month names to numbers (ie 1 for
Jan, 2 for Feb, etc) in the headings in H2:S2. Since the spreadsheet needs
to be setup with month names instead of numbers, I just used a different cell
(and then hid it) to convert the month(c5) into a name using TEXT function.

Thanks for getting me on the right track.
Marcia

"Office User" wrote:

It still gives me #VALUE! as the result. Sorry.

"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH($H$2:$S$2)<=MONTH(C5)),H5:S5)


"Office User" wrote:

I'm trying to figure year to date totals based on month. The formula needs
to figure a person's total hours of vacation used since anniversary month
(which is just the month of their hire date). I have a spreadsheet with
month headings Jan thru Dec in H2:S2 with vacation hours taken each month by
each employee in rows under these headings. It looks something like this:

Jan Feb Mar . . .
10 0 10
0 8 0
2 0 0

If the first person started working in March, their total YTD needs to just
add months March through Dec, but the next person may have started in Sept so
that line needs to add months Sept through Dec.

With hire date in column C, month headings in H2:S2, and data in row 5,
here's the formula I have tried but receive #VALUE!

=SUMPRODUCT(--(--(MONTH($H$2:$S$2))<=(MONTH(C5))),--(--(H5:S5)))

Any help will be greatly appreciated. Thanks,
Marcia

Check to make sure your H2:S2 and C5 are the real date
ie =COUNT(H2:S2)=12
If it is a real date it will return TRUE, else FALSE

"Office User" wrote:

It still gives me #VALUE! as the result. Sorry.

"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH($H$2:$S$2)<=MONTH(C5)),H5:S5)


"Office User" wrote:

I'm trying to figure year to date totals based on month. The formula needs
to figure a person's total hours of vacation used since anniversary month
(which is just the month of their hire date). I have a spreadsheet with
month headings Jan thru Dec in H2:S2 with vacation hours taken each month by
each employee in rows under these headings. It looks something like this:

Jan Feb Mar . . .
10 0 10
0 8 0
2 0 0

If the first person started working in March, their total YTD needs to just
add months March through Dec, but the next person may have started in Sept so
that line needs to add months Sept through Dec.

With hire date in column C, month headings in H2:S2, and data in row 5,
here's the formula I have tried but receive #VALUE!

=SUMPRODUCT(--(--(MONTH($H$2:$S$2))<=(MONTH(C5))),--(--(H5:S5)))

Any help will be greatly appreciated. Thanks,
Marcia

I posted that I had it working after changing my headings from month names to
numbers. It does add but is adding all the months. If C5 is 7/14/05 then it
should only add the numbers under columns July through December. Here's the
formula currently being used.
=SUMPRODUCT(--(MONTH($H$2:$S$2)<=MONTH(C5)),H5:S5)

In addition, they've now given me a twist. The way they've previously used
the spreadsheet is to list only the previous 12 months. So this month, it
will show Jan 06 through Dec 06 but next month it will show Feb 06 through
Jan 06. Since each person's vacation total is figured for a whole year but
based on the month they started will SUMPRODUCT be able to distinguish this?

Thanks again,
Marcia

"Teethless mama" wrote:

Check to make sure your H2:S2 and C5 are the real date
ie =COUNT(H2:S2)=12
If it is a real date it will return TRUE, else FALSE

"Office User" wrote:

It still gives me #VALUE! as the result. Sorry.

"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH($H$2:$S$2)<=MONTH(C5)),H5:S5)


"Office User" wrote:

I'm trying to figure year to date totals based on month. The formula needs
to figure a person's total hours of vacation used since anniversary month
(which is just the month of their hire date). I have a spreadsheet with
month headings Jan thru Dec in H2:S2 with vacation hours taken each month by
each employee in rows under these headings. It looks something like this:

Jan Feb Mar . . .
10 0 10
0 8 0
2 0 0

If the first person started working in March, their total YTD needs to just
add months March through Dec, but the next person may have started in Sept so
that line needs to add months Sept through Dec.

With hire date in column C, month headings in H2:S2, and data in row 5,
here's the formula I have tried but receive #VALUE!

=SUMPRODUCT(--(--(MONTH($H$2:$S$2))<=(MONTH(C5))),--(--(H5:S5)))

Any help will be greatly appreciated. Thanks,
Marcia