 SOLVED

# Using COUNTIF from a VLOOKUP

Hi,

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 column...at 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?

TIA

6 Replies

# Re: Using COUNTIF from a VLOOKUP

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

If so, use a formula such as

=SUMPRODUCT(--ISNUMBER(D2:D100))

where D2:D100 is the range with the formulas.

# Re: Using COUNTIF from a VLOOKUP

Thanks.

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...

e.g.

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?

TIA

# Re: Using COUNTIF from a VLOOKUP

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

# Re: Using COUNTIF from a VLOOKUP

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

=VLOOKUP(A1,C:E,3,FALSE)

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?

TIA
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Using COUNTIF from a VLOOKUP

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.

# Re: Using COUNTIF from a VLOOKUP

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