"#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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,782
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Do you have any errors or text values in A84:A400 on the Daily rundown sheet?
 

Windyisle

New Member
Joined
Feb 16, 2011
Messages
9
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,782
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,015
Messages
5,834,938
Members
430,327
Latest member
Mr_Himalayan778

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
Top