CODEWITHSUNDEEP
excel
Uvulagirl
Uvulagirl

Reputation: 17

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

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

Upvotes: 0

Views: 205

Answers (1)

QHarr
QHarr

Reputation: 84465

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.

Upvotes: 0

Related Questions