COUNTIFS Error

I am using the following formula:

=COUNTIFS(Data!$I:$I,">"&B38, Data!$I:$I,"<"&C38+1,Data!$D:$D,"?*",Data!$B:$B,"")

Data!$I:$I points to the contact date
B38 points to the first of the month
C38 points to the last of the month
Data!$D:$D points a column that has a number or is blank
Data!$B:$B points to a name or is blank

My goal is to count the records with a contact date between first and last of the month, with column D not blank and column B blank. The formula is returning zero records, but there should be records.

It also returns 0

"Why not use a formula to determine the month."

I don't know how to do that.

You'll have to break it down into parts to see which part isn't working...

Do each of these return results you would expect them to ? Which one(s) doesn't ?
=COUNTIFS(Data!$I:$I,">"&B38)
=COUNTIFS(Data!$I:$I,"<"&C38+1)
=COUNTIFS(Data!$D:$D,"?*")
=COUNTIFS(Data!$B:$B,"")

Originally Posted by Jonmo1

You'll have to break it down into parts to see which part isn't working...

Do each of these return results you would expect them to ? Which one(s) doesn't ?
=COUNTIFS(Data!$I:$I,">"&B38)
=COUNTIFS(Data!$I:$I,"<"&C38+1)
=COUNTIFS(Data!$D:$D,"?*")
=COUNTIFS(Data!$B:$B,"")

It's =COUNTIFS(Data!$D:$D,"?*") that isn't functioning.

Originally Posted by martindwilson

whats in col d?

account numbers

Originally Posted by Tony Valko

They're slightly different.

Data!$B:$B,"=")

That will only count EMPTY cells.

Data!$B:$B,"")

That will count both cells that are empty and cells that contain formula blanks.


I think we need some clarification on what "blank" means. Does blank mean empty cells or cells that contain formula blanks?

blank means empty

Originally Posted by whizbee

I am using the following formula:

=COUNTIFS(Data!$I:$I,">"&B38, Data!$I:$I,"<"&C38+1,Data!$D:$D,"?*",Data!$B:$B,"")

Data!$I:$I points to the contact date
B38 points to the first of the month
C38 points to the last of the month
Data!$D:$D points a column that has a number or is blank
Data!$B:$B points to a name or is blank

My goal is to count the records with a contact date between first and last of the month, with column D not blank and column B blank. The formula is returning zero records, but there should be records.

I ended up using this formula:
=COUNTIFS(Data!$H:$H,">="&B2,Data!$I:$I,">="&B2,Data!$I:$I,"<"&C2+1,Data!$B:$B,"")

Column D has account numbers, and column H has account creation dates, so it should be fine. But I still wasn't able to figure out how to count only records with a number in column D.