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

Question

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

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
– girlvsdata, Commented Aug 23, 2018 at 1:25

QHarr · Accepted Answer · 2018-08-23 05:43:05Z

0

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.

edited Aug 23, 2018 at 5:43

answered Aug 22, 2018 at 6:20

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Uvulagirl Over a year ago

Thankyou for your response but I am still receiving the accumulative count of the previous months in the future month fields for Sept, Oct, Nov and Dec. I need these fields to show "0" until dates are added for those months in Column A (in the future). I have updated my original post with the link to the Example sheet.

QHarr Over a year ago

They should appear as 0 because they fail "<"&EOMONTH(D1,0)+1 test. Nothing would then be summed. Can you please show some data including an example that fails. Unless, you have the dates already present but no data in the field? In which case you can't use the date field to restrict future entries. Is there a field which is always populated historically but is never populated in advance?

Uvulagirl Over a year ago

Thankyou! The final formula using IF(TODAY()... function works as I need it to! I have edited it slightly to the below as it doesn't need the calendar year constraint. =IF(TODAY()>EDATE(D1,-1),COUNTIFS($B:$B,"<>x",$A:$A,"<"&EOMONTH(D1,0)+1),0)

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related