CODEWITHSUNDEEP
excelfunctiondatecount
SanLuka
SanLuka

Reputation: 125

Counting Cells in a Range that Contains Formulas, Only when a Date is Returned?

Frequent browser, first time poster. Please go easy:

I have spent the last few days searching online, and on here for a solution to a problem I have encountered for the first time. I have a report that pulls from multiple worksheets. One column is a formula that does a VLOOKUP to another sheet and pulls back a date, if it exists. I then have a cell at the top of the sheet that calculates how many dates are pulled back out of all of the rows (to calculate % complete). This is where I am having the problem. I have tried variations of COUNTIF, COUNTA, COUNTBLANK, and so on, and formulas trying to reverse calculate, 

=SUM(C4)-COUNTIF(Table3[2014 Process Date],"")

At first it appeared to work, but in this example, I had 1949 rows, and dates only populated in 7 of those rows. In theory it should return 7. Instead it is returning 237. I have done multiple filters, and manually reviewed the data in the column, and only 7 dates are there. The column has the VLOOKUP in and IFERROR nest, 

=IFERROR(VLOOKUP(A12,Table_TaxData.accdb3[#All],240,FALSE),"").

I am guessing I am overlooking something silly, and was hoping someone would be able to help steer me in the right direction, or let me know what I am missing. Thanks in advance for any help!

Upvotes: 0

Views: 86

Answers (2)

SanLuka
SanLuka

Reputation: 125

Wow, looks like I need some more coffee! Thank you, I guess I assumed that it would be much more complicated than that. I just threw in

=COUNT(Table3[2014 Process Date])

And it worked like a charm! Thanks again!

Upvotes: 1

Ditto
Ditto

Reputation: 3344

If I'm reading your formula correctly, the target cells hold either the DATE, or a blank "".

If so, you can do a COUNTIF and do this:

=COUNT(B:B)

to get # of dates. or

=COUNTA(B:B)-COUNT(B:B)

to get # of blanks.

(I used column B, not sure where your final values are in you're looking for - adjust accordingly)

Upvotes: 0

Related Questions