Using Countif, Vlookup and Importrange

Question

I'm currently struggling with using the Countif, Vlookup and Importrage formula on Google Sheets...

I need to pull data from the raw data sheet to the data set that I'm using and can't get it right.

Formula that I have is as follows:

=countif(vlookup(A2,IMPORTRANGE("URL","Data!A2:I940"),9,false),"supplier")

I need to look up the date and then get the count for how many "Suppliers" we had on that specific dates..

Anyone having the same issue or is my brain just over worked???

What is the value in A2, and can you provide a screengrab of sample data Data!A1:I5 ? The VLOOKUP not working might be an issue of numbers being treated as text. — Aresvik
A2's value is the date (Example 27/03/2021). All of the data on the sample data is text. I need the formula to count the word supplier (if that makes sense). — Carl Du Plooy
Here's the screengrab: drive.google.com/file/d/1cf92B9zo36BYVuH_HgPnv4DIeTE5nV_x/… — Carl Du Plooy
Can you share the image and if possible a copy of the spreadsheet publicly? — Kessy
Dates are stored as numbers, so 27/3/2021 will be 44282. If your VLOOKUP is trying to find that number in column Data!A:A, which is formatted as text, then I think that might be the issue. Suggest you format them both to be numbers. — Aresvik

marikamitsos marikamitsos · Accepted Answer · 2021-05-13T11:51:34

I think a better choice would be to use a query formula instead.
Please follow the logic of this given formula

=QUERY({B1:D12},"select count(Col1) 
                 where Col3=date'"&TEXT(F1,"yyyy-mm-dd")&"' and Col2='"&F2&"'
                 label count(Col1) '' ")

(Do adjust ranges to your needs)

Using Countif, Vlookup and Importrange

1 Answers