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