"#Value" error with Sumproduct

Windyisle

New Member
Joined
Feb 16, 2011
Messages
9
Hey there,

I"m so confused. I have a sumproduct formula that works seemingly only if I keep the number of cells it's searching below a certain range.

This works fine:
=SUMPRODUCT(--(WEEKDAY('Daily Rundown'!$A$5:$A$83,2)=1),--(ISNUMBER(SEARCH("*advocate*",'Daily Rundown'!$F$5:$F$83))),--(ISNUMBER(SEARCH("00:00:15:00",'Daily Rundown'!$D$5:$D$83))))

This returns the '#Value' error:
=SUMPRODUCT(--(WEEKDAY('Daily Rundown'!$A$5:$A$400,2)=1),--(ISNUMBER(SEARCH("*advocate*",'Daily Rundown'!$F$5:$F$400))),--(ISNUMBER(SEARCH("00:00:15:00",'Daily Rundown'!$D$5:$D$400))))

If more information on my worksheet is needed: I'm extracting data to tables, one for each day of the week, from an output list of the whole week. I can go through and guess at where Monday might stop and start and do the same for Tuesday...etc. (which is why it goes just from A5 to A83 in the first example) But I have all kinds of different data to extract and that would mean tediously changing the numbers in the formula on each daily table, rather than just quickly changing the weekday number.

Any help would be much appreciated.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Do you have any errors or text values in A84:A400 on the Daily rundown sheet?
 
Upvote 0
That's it!

The last entry in that column is 'page 1'. I'm not sure what to do about this. I'll be pasting in a new week every week and each one will be a different length. Is there a way I can get it to ignore the very last cell if the database is of variable length? Sort of like a 'select all then -1' from the range to look in?

Thanks for the help.
 
Upvote 0
You can use a formula like:
=MATCH(9E+300,A:A,1)
to find the last row in A containing a number value (dates are numbers), and then use that row number in your formulas.
 
Upvote 0

Forum statistics

Threads
1,224,594
Messages
6,179,795
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top