SUMPRODUCT Error

[jwah]

This thread will be tricky to explain, but here goes...

I have built up the following formula, which works it resides in multiple cells. I have structured it so dragging it down it picks up the correct cell to match on...

Formula goal for the month referenced e.g. in X15 go to the data SHEET and for that person, return the total number of hours.

=IF($D73<>"",SUMPRODUCT((TEXT(data!$A$2:$A$1000,"mmm-yy")=TEXT(X$15,"mmm-yy"))*--ISNUMBER(SEARCH($D73,data!$J$2:$J$1000)),data!$N$2:$N$1000),0)

The formula (also) resides horizontally i.e. in
Y73 = ok this is Apr-11
W73 = ok this is Mar-11
S73 = ok this is Feb-11
Q73 = returns 0, but this is wrong (Jan-11)
O73 = returns 0, but this is wrong (Dec-10)

Call information:

X15 has a date 1/1/11 (formated as mmm-yy)
D73 has a name, e.g. Smith, John
Data!Column A has dates, e.g. 21/1/11
Data!Column J has person names in the format lastname, firstname but prefixed with other chars so hence SEARCH (which works ok).
Data!ColumnN has the number of hours e.g. 8 performed on that date

I have manually checked the totals and data is ok and Jan-11 and Dec-10 should returns a value much greater than 0!

(Separately I also get an error #N/A when I change the range from 1000 rows to (say) 5000!)

jwah

 

[T. Valko]

This thread will be tricky to explain, but here goes...

I have built up the following formula, which works it resides in multiple cells. I have structured it so dragging it down it picks up the correct cell to match on...

Formula goal for the month referenced e.g. in X15 go to the data SHEET and for that person, return the total number of hours.

=IF($D73<>"",SUMPRODUCT((TEXT(data!$A$2:$A$1000,"mmm-yy")=TEXT(X$15,"mmm-yy"))*--ISNUMBER(SEARCH($D73,data!$J$2:$J$1000)),data!$N$2:$N$1000),0)

The formula (also) resides horizontally i.e. in
Y73 = ok this is Apr-11
W73 = ok this is Mar-11
S73 = ok this is Feb-11
Q73 = returns 0, but this is wrong (Jan-11)
O73 = returns 0, but this is wrong (Dec-10)

Call information:

X15 has a date 1/1/11 (formated as mmm-yy)
D73 has a name, e.g. Smith, John
Data!Column A has dates, e.g. 21/1/11
Data!Column J has person names in the format lastname, firstname but prefixed with other chars so hence SEARCH (which works ok).
Data!ColumnN has the number of hours e.g. 8 performed on that date

I have manually checked the totals and data is ok and Jan-11 and Dec-10 should returns a value much greater than 0!

(Separately I also get an error when I change the range from 1000 rows to (say) 5000!)

jwah

Are you getting an error or an incorrect result?

Write the formula like this:

=IF($D73<>"",SUMPRODUCT(--(TEXT(data!$A$2:$A$1000,"mmm-yy")=TEXT(X$15,"mmm-yy")),--(ISNUMBER(SEARCH($D73,data!$J$2:$J$1000))),data!$N$2:$N$1000),0)

 

[jwah]

Hi,
Thanks

I am getting 0 (zero), no error. Using Excel 2003.

Here is the formula that resides in Q73

=IF($D73<>"",SUMPRODUCT((TEXT(data!$A$2:$A$1000,"mmm-yy")=TEXT(P$15,"mmm-yy"))*--ISNUMBER(SEARCH($D73,data!$J$2:$J$1000)),data!$N$2:$N$1000),0)

 

[T. Valko]

I am getting 0 (zero)

Here is the formula that resides in Q73

=IF($D73<>"",SUMPRODUCT((TEXT(data!$A$2:$A$1000,"mmm-yy")=TEXT(P$15,"mmm-yy"))*--ISNUMBER(SEARCH($D73,data!$J$2:$J$1000)),data!$N$2:$N$1000),0)

Ok, well, that means you probably have "data problems". Dates may not be true Excel dates, the numbers to sum may not be true Excel numbers.

See this:

http://contextures.com/xlFunctions02.html#Trouble

Even though that link is for a different function the problems it describes apply to all functions.

While your formula does work the correct syntax is:

=IF($D73<>"",SUMPRODUCT(--(TEXT(data!$A$2:$A$1000,"mmm-yy")=TEXT(P$15,"mmm-yy")),--(ISNUMBER(SEARCH($D73,data!$J$2:$J$1000))),data!$N$2:$N$1000),0)

 

[jwah]

Fixed. I had to extend the range (out to 2500 rows). I note I had to do this on all three values across the formula; i.e. until I changed all three I got an #N/A error. All good and thank you for your guidance.

 

[T. Valko]

Fixed. I had to extend the range (out to 2500 rows). I note I had to do this on all three values across the formula; i.e. until I changed all three I got an #N/A error. All good and thank you for your guidance.

Good deal. Thanks for the feedback!