#Value! Error Using SumProduct

I am working with a dataset consisting of email addresses, blank spaces, dates, and various other information. I am using a SumProduct that checks a Date column, ensuring that the date is no earlier than the present day, and also checks an email column to ensure that there is an email address there. I need a count of the total email addresses in a specific column based on the date. The formula I'm using now returns the #value! error:

C43 is the date that the table was last updated in the numeral form (6/19/2018 = 43270). Overall_Pop_Date is a dynamic range that looks as so:

Overall_Pop_TPA is another dynamic range that is used to help define the range where the email addresses will fall:

The table of dates and email addresses will be added to and lengthened which is why the dynamic range is necessary.

I believe the error is coming from the blank cells in the column after the search operation is performed. With a blank cell, the search operation would return an error value. I thought the isnumber operator would fix this, reading the error as false, which is why I'm stumped.

PS: I can't provide the data table as it would be a violation of our privacy practices, however I'm happy to describe the table in more detail.

I would love to share the actual formula's thaI'm using but because I am such a new user, the website does not allow me to post formula's as text. I could PM the formula's however if that would make it easier?

I don't think a countif would be easier, maybe that just because I'm not sure how to use countif combined with istext but the sumproduct way has been working all throughout the rest of my workbook so I'm not sure why I'm struggling with this particular issue.

A couple of thoughts:-

Are the dynamic ranges all of equal size? If not that could cause the formula to fail, although I would think with a #N/A rather than #Value error.

Have you missed out, or misplace some of the parentheses in the formula? This could cause text (including any formula in the table that returns a blank) to be incorrectly evaluated as numeric, which would be one of the more likely causes of the error you are seeing.

As I could see no other relevance to it, I assume that C43 should be used as the 'present day' for the formula purpose, rather than actaul date =TODAY()

Based on your description in your original post, your formula should look something like.

PHP Code: 

=SUMPRODUCT((Overall_Pop_Date>=C43)*(Overall_Pop_TPA<>"")) 


Which as Ford suggested might be better replace with countifs

PHP Code: 

=COUNTIFS(Overall_Pop_Date,">="&C43,Overall_Pop_TPA,"<>") 


Does that help?

I tried the sumproduct that you proposed:
=SUMPRODUCT((Overall_Pop_Date>=C43)*(Overall_Pop_TPA<>""))

however it did not work Jason.b75, instead of a #value! error now it is a #N/A error

Sambo Kid, I am not receiving a Sucuri error as far as I know, everything I know has been posted.

I PM'd FDibbins my formulas so hopefully he posts those soon so that they can I've you more perspective on the problem. Appreciate the help so far everyone!

That implies that the two dynamic ranges are not of equal size.

I don't see why you can't post formulas, another member who only joined today was able to do so.

There are problems with certain combinations of < and > being misinterpreted by the board software as html, of some other form of browser code. Copy and paste your formula to the forum, but insert a space before and after any occurrence of < or > before trying to submit your post.

In addition to the formula you are trying to use in your sheet, the formulas used to define the named ranges may also be needed in order to find the root of the problem.

I'll try again,

the error keeps saying I cant post any kinds of links, image or videos until I post a few more times.

I'll keep trying

How are you trying to post the formula?

You need to click in the formula bar and copy the text of the formula, not just copy the cell.

I know the defined dynamic ranges are the same length, so that can't be the issue.

This one is good, I accidentally sent twice, sorry I'm new here lol

hold on please

Thanks, Ford, I just got the PM as well, you beat me too it.

Aside from something in the date column that cannot convert to a valid date, which could be tripping a #Value! error, the only possible thing that I can think of is a discrepancy in the named ranges, which would be tripping a #N/A error. If the formula is returning both, I'm not sure which wouls show first.

Even though you're sure that both dynamic ranges are the same, with your method, a row that is blank in column M with an entry in column K, or vice versa, would cause an error. I would suggest changing one so that the counts are both done on the same column, as a failsafe, for example.

=OFFSET('Overall Delivery Population'!$K$2,0,0,COUNTA('Overall Delivery Population'!$M$2:$M$4999),1)

Although personally, I would use

='Overall Delivery Population'!$K$2:INDEX('Overall Delivery Population'!$K$K,Last_Row)

Where Last_Row is another named range, defined as

=MATCH("ZZZZ",'Overall Delivery Population'!$M:$M)

Which would prevent additional possible errors when your table is near full with intermittent blanks. The match should be done in a column where blanks should never occur, using "ZZZZ" as the criteria for text fields, or 1e+100 for numeric.

Alright everyone, thank you all for your help! after splitting up the sumproduct like FDibbins had suggested, I saw that the dynamic defined ranges were not lining up like I thought they were supposed to. Although I had the counta() range within the dynamic ranges set to the same distance, they were coming up with different counts due to the different columns they were measuring. I now have each dynamic range set to the same length, and each sum product is working correctly with no error. Thanks again for the help everyone!

Please see my last reply with refernce to dynamic named range suggestions.

Now that you have that resolved, perhaps the countifs suggestion will also work, not knowing the format of the source data it is near impossible to know if the DATEVALUE(TEXT( part of your formulas is necessarry, or if it could be omitted to make the formula more efficient.