Sumproduct not working when referencing a column of formulas

[sskgintl]

I am updating our membership tracking spreadsheet to give me a month end summary instead of current snapshot using this formula:


=SUMPRODUCT(--('Members 2015-16'!K2:K293>0),--('Members 2015-16'!Q2:Q293<=(EOMONTH(TODAY(),-1))))

simply changing the red cell reference for the different columns I need data from. It works perfectly right up until column K. The difference between column K and the others is that column K contains the formula below rather than simple data.

=IF(AND(ISBLANK(E2),ISBLANK(J2)),"",IF(J2>0,0,IF(D2-250-E2<0,0,D2-250-E2)))

Any ideas would be greatly appreciated.

[FlameRetired]

I only have an observation.

=SUMPRODUCT(--('Members 2015-16'!K2:K293>0),--('Members 2015-16'!Q2:Q293<=(EOMONTH(TODAY(),-1))))

appears that it could use an additional criteria for the lower boundary of the date range.

=SUMPRODUCT(--('Members 2015-16'!K2:K293>0),--('Members 2015-16'!Q2:Q293>EOMONTH(TODAY(),-2)),--('Members 2015-16'!Q2:Q293<=(EOMONTH(TODAY(),-1))))

or whatever the appropriate column reference is.

It's difficult to say without an uploaded Excel workbook example.

If you are not familiar with how to do that instructions are here:

http://www.excelforum.com/forum-rule...rum-rules.html

[sskgintl]

Thanks, Dave. If I wanted one month of data only, that is exactly how I would do it. I use the data from the beginning of fiscal year through the end of the month, hence only the one constraint.

I actually just had a brain storm and figured out how to solve this by adding an additional criteria. The cells where the formula was returning "" had to be the problem, so I added a criteria that they could not be equal to ""

=SUMPRODUCT(--('Members 2015-16'!$K$2:$K$293>0),--('Members 2015-16'!$K$2:$K$293<>""),--('Members 2015-16'!$Q$2:$Q$293<=(EOMONTH(TODAY(),-1))))

[FlameRetired]

Good to hear.