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

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
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
35,503
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,970
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top