SUMPRODUCT ERROR

[ColleenK]

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

 

[T. Valko]

This is causing the error:

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

Should that maybe be:

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

 

[David Biddulph]

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

 

[Eduardo]

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

 

[Tom Hutchins]

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]

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

 

[ColleenK]

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

 

[ColleenK]

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

 

[Tom Hutchins]

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$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