Hi There,

I am getting a value error when using the following formula:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),--('Detail Hours'!P7=A5),('Detail
Hours'!$P$1:$P$1000))

Where the first criteria matches a name and the next criteria matches a date.

Please help.

Thanks
--
CK

This is causing the error:

--('Detail Hours'!P7=A5)

Should that maybe be:

--('Detail Hours'!P1:P1000=A5)

--
Biff
Microsoft Excel MVP


"ColleenK" wrote in message
...
Hi There,

I am getting a value error when using the following formula:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),--('Detail Hours'!P7=A5),('Detail
Hours'!$P$1:$P$1000))

Where the first criteria matches a name and the next criteria matches a
date.

Please help.

Thanks
--
CK

Presuamably some of the values in column P are text, rather than numbers?
--
David Biddulph

"ColleenK" wrote in message
...
Hi There,

I am getting a value error when using the following formula:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),--('Detail Hours'!P7=A5),('Detail
Hours'!$P$1:$P$1000))

Where the first criteria matches a name and the next criteria matches a
date.

Please help.

Thanks
--
CK

Hi,
your problem is that the ranges has to be the same within the formula try this

=IF('Detail Hours'!P7=A5,SUMPRODUCT(--('Detail
Hours'!$F$1:$F$1000=D$3),--('Detail Hours'!$F$1:$F$1000<""),('Detail
Hours'!$P$1:$P$1000)))

"ColleenK" wrote:

Hi There,

I am getting a value error when using the following formula:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),--('Detail Hours'!P7=A5),('Detail
Hours'!$P$1:$P$1000))

Where the first criteria matches a name and the next criteria matches a date.

Please help.

Thanks
--
CK

You are getting an error because each argument in a SUMPRODUCT formula has to
be an array (range) containing the same number of cells. Your other arguments
each has a range of 1000 cells, but --('Detail Hours'!P7=A5) refers to a
single cell. You have to pull that out of the SUMPRODUCT function and
incorporate it separately:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),('Detail Hours'!$P$1:$P$1000)) *('Detail
Hours'!P7=A5)

However, some of this doesn't make sense (to me, at least). You are testing
that F1:F1000 is equal to D3. Then you are testing that F1:F1000 is not
empty. Because of the first test, the second test is really just testing that
D3 is not empty.

It looks like your formula is counting the number of records for the person
whose name is in D3, but only if the date in P7 matches the date in A5.
Unless all the records for the D3 person happen to have the same date as P7,
you are not counting the records for that person with that date. What are you
trying to accomplish with this formula? I am afraid it may not be doing what
you intended.

Hope this helps,

Hutch

"ColleenK" wrote:

Hi There,

I am getting a value error when using the following formula:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),--('Detail Hours'!P7=A5),('Detail
Hours'!$P$1:$P$1000))

Where the first criteria matches a name and the next criteria matches a date.

Please help.

Thanks
--
CK

Thanks for the suggestion, unfortunately changing the range does not work, as
the date is only in one cell, the formula returns zero.
--
CK


"T. Valko" wrote:

This is causing the error:

--('Detail Hours'!P7=A5)

Should that maybe be:

--('Detail Hours'!P1:P1000=A5)

--
Biff
Microsoft Excel MVP


"ColleenK" wrote in message
...
Hi There,

I am getting a value error when using the following formula:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),--('Detail Hours'!P7=A5),('Detail
Hours'!$P$1:$P$1000))

Where the first criteria matches a name and the next criteria matches a
date.

Please help.

Thanks
--
CK


.

Thanks for the suggestion, I had tried this but it does not give me what I
need, as there are lots of columns.
--
CK


"Eduardo" wrote:

Hi,
your problem is that the ranges has to be the same within the formula try this

=IF('Detail Hours'!P7=A5,SUMPRODUCT(--('Detail
Hours'!$F$1:$F$1000=D$3),--('Detail Hours'!$F$1:$F$1000<""),('Detail
Hours'!$P$1:$P$1000)))

"ColleenK" wrote:

Hi There,

I am getting a value error when using the following formula:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),--('Detail Hours'!P7=A5),('Detail
Hours'!$P$1:$P$1000))

Where the first criteria matches a name and the next criteria matches a date.

Please help.

Thanks
--
CK

column F has a list of names and columns P thru BT are hours associated with
the name, each column in P thru BT has a date as a heading. I am trying to
summarize the data into another worksheet. Hope this helps
--
CK


"Tom Hutchins" wrote:

You are getting an error because each argument in a SUMPRODUCT formula has to
be an array (range) containing the same number of cells. Your other arguments
each has a range of 1000 cells, but --('Detail Hours'!P7=A5) refers to a
single cell. You have to pull that out of the SUMPRODUCT function and
incorporate it separately:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),('Detail Hours'!$P$1:$P$1000)) *('Detail
Hours'!P7=A5)

However, some of this doesn't make sense (to me, at least). You are testing
that F1:F1000 is equal to D3. Then you are testing that F1:F1000 is not
empty. Because of the first test, the second test is really just testing that
D3 is not empty.

It looks like your formula is counting the number of records for the person
whose name is in D3, but only if the date in P7 matches the date in A5.
Unless all the records for the D3 person happen to have the same date as P7,
you are not counting the records for that person with that date. What are you
trying to accomplish with this formula? I am afraid it may not be doing what
you intended.

Hope this helps,

Hutch

"ColleenK" wrote:

Hi There,

I am getting a value error when using the following formula:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),--('Detail Hours'!P7=A5),('Detail
Hours'!$P$1:$P$1000))

Where the first criteria matches a name and the next criteria matches a date.

Please help.

Thanks
--
CK

Does this version do what you want?

=IF(AND(LEN($D$3)0,'Detail Hours'!P7=$A5),SUMPRODUCT(--('Detail
Hours'!$F$1:$F$1000=D$3),('Detail Hours'!P$1:P$1000)),0)

I'm not sure which references need to be absolute vs. relative for your
purposes. Are you going to enter this in a column on another sheet, then copy
it across 56 more columns?

Hope this helps,

Hutch

"ColleenK" wrote:

column F has a list of names and columns P thru BT are hours associated with
the name, each column in P thru BT has a date as a heading. I am trying to
summarize the data into another worksheet. Hope this helps
--
CK


"Tom Hutchins" wrote:

You are getting an error because each argument in a SUMPRODUCT formula has to
be an array (range) containing the same number of cells. Your other arguments
each has a range of 1000 cells, but --('Detail Hours'!P7=A5) refers to a
single cell. You have to pull that out of the SUMPRODUCT function and
incorporate it separately:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),('Detail Hours'!$P$1:$P$1000)) *('Detail
Hours'!P7=A5)

However, some of this doesn't make sense (to me, at least). You are testing
that F1:F1000 is equal to D3. Then you are testing that F1:F1000 is not
empty. Because of the first test, the second test is really just testing that
D3 is not empty.

It looks like your formula is counting the number of records for the person
whose name is in D3, but only if the date in P7 matches the date in A5.
Unless all the records for the D3 person happen to have the same date as P7,
you are not counting the records for that person with that date. What are you
trying to accomplish with this formula? I am afraid it may not be doing what
you intended.

Hope this helps,

Hutch

"ColleenK" wrote:

Hi There,

I am getting a value error when using the following formula:

=SUMPRODUCT(--('Detail Hours'!$F$1:$F$1000=D$3),--('Detail
Hours'!$F$1:$F$1000<""),--('Detail Hours'!P7=A5),('Detail
Hours'!$P$1:$P$1000))

Where the first criteria matches a name and the next criteria matches a date.

Please help.

Thanks
--
CK