New Contributor



I have a column of data in excel which is based on a VLOOKUP bringing back various data items (dates mainly but some #n/a as expected) from another data source.


What i am trying to do is count the number of times a date is is then brought back to that the moment it always brings back 0 as the cells have the formula which i need to keep dynamic


Is it possible to COUNTIF the VLOOKUP data that is returned?



6 Replies


So you basically want to count the cells that do not show #N/A ?

If so, use a formula such as




where D2:D100 is the range with the formulas.

@Hans Vogelaar 




I need to count the data based on a COUNTIF.


So if the column with the VLOOKUP has various dates, I am looking to count those individual dates...




Column D (which is where the VLOOKUP is) may bring back 04/04/21, 05/04/21, NA, 12/03/21, 05/04/21 etc


I am looking to carry out a COUNTIF using these dates to see how many times each date is then


So i am using =COUNTIF(D:D,A1) (where A1 would have a date - say 05/04/21 for example) - so should being back the count of 2 on the above list


Does that make sense?




The formula looks OK, whether column D contains formulas or not. Do you have a problem with it, and if so, which problem?

So in Sheet 1, Column D, i have something like


Column D is then showing dates (04/04/21, 05/04/21, NA, 12/03/21, 05/04/21 etc) for the number of rows i am looking up

Then in Sheet 2 i am then trying to COUNTIF:

Column A would have the list of dates (04/04/21, 05/04/21,12/03/21)
And then column B using the formula =COUNTIF(Sheet1!D:D,A2)

I would expect it to come back with 2 as before, but it just returns 0.
If i change a cell and hard type in the date - it does count it.

So it appears that the returned data from the VLOOKUP will not count, which is where i am stuck.

Does that make sense?

best response confirmed by allyreckerman (Microsoft)


Does column E on Sheet 1 contain 'real' dates, or text values that look like dates?

Or are they dates that have a time component?

Same question for column A on Sheet 2.

Have checked and made changes - thanks for all your help. Its working now.