SOLVED

Using COUNTIF from a VLOOKUP

New Contributor

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

@HelenC7 

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.

@Hans Vogelaar 

 

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

@HelenC7 

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

=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

@HelenC7 

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.