MS Excel How to return BLANK value in COUNTIF formula if date doesn't appear in range

0
votes

I am reporting the accumulative count of dates that appear in Column for each month for a year (eg 2018). I have used a COUNTIFS function to exclude the cell from the count if there is an 'x' next to it in Column B.

=COUNTIFS($B:$B,"<>x",$A:$A,"<="&EDATE(D1,0))

It works perfectly but for future months that haven't occurred yet (eg Sept, Oct, Nov, Dec) I need the formula to return "0" or blank. See attached example sheet.

I have tried to nest the COUNTIFS in an IF(AND) formula but it just doesn't want to work! I keep getting a ERROR value. SUMPRODUCT doesn't appear to work either.

Accumm COUNTIFS Test Sheet.xls

1
excel
Instead of a Google Drive link, please edit your question to include a text example of data you might see in your spreadsheet. See this question for a good example. – girlvsdata

1 Answers

0
votes

I have added a date condition below stating for column A to be less than the first of next month based on what is in column D:

=COUNTIFS($B:$B,"<>x",$A:$A,"<"&EOMONTH(D1,0)+1)

With a same calendar year constraint:

=COUNTIFS($B:$B,"<>x",$A:$A,"<"&EOMONTH(D1,0)+1,$A:$A,">=" & DATE(YEAR(D1),1,1))

Perhaps try:

=IF(TODAY()>EOMONTH(D1,0),"",COUNTIFS($B:$B,"<>x",$A:$A,"<"&EOMONTH(D1,0)+1,$A:$A,">=" & DATE(YEAR(D1),1,1)))

If you already have future dates in the sheet.