using “like” with cell to reference the last four digits in google sheets

Question

hardCode formula dynamic formula I am using google sheets to make a report related to customers projects,

in my company When we have new project we give the project a 4 digits number then we create account receivable for the customer contain of 14 digits like that : the first 6 digits is fixed the second 4 digits represent the year and month the last 4 digits represent the project number

so i have column with the Project number and i want to make a query that return the count of costumer payment in front of the project number , when i try to use this formula :

=QUERY({'2016DB'!$A$2:$H$18168;'2018DB'!$A$2:$H$13379;'2017DB'!$A$2:$H$17759;'2019DB'!$A$2:$H},"select count(Col3) where Col1 = 12040118091492 And Col3 > 0 label count(Col3) '' ",0)

it's work fine

But when i try to make it dynamic by using like statement :

QUERY({'2016DB'!$A$2:$H$18168;'2018DB'!$A$2:$H$13379;'2017DB'!$A$2:$H$17759;'2019DB'!$A$2:$H},"select count(Col3) where Col1 like 120401____"""&E7&""" And Col3 > 0 label count(Col3) '' ",0)

Where E7 Cell Contain the Project number it doesn't work !!

Could you make a screenshot of what you currently have and what you need t achieve? — ILya Cyclone

ILya Cyclone ILya Cyclone · Accepted Answer · 2019-02-10T21:59:57

Made a simple example with your type of query. Check this, please: https://docs.google.com/spreadsheets/d/1ISG0AjMNhwnXQvlxDJCMHcyx8mDKiCv6zqOfAGxrrDg/edit?usp=sharing (screenshot: https://i.imgur.com/jHppqzr.png)

Main formula is: =QUERY(A1:A5, "select A where A like 'a__"&D1&"'") where D1 is your needed project number. Looked up values are in format: letter a followed by 2 any characters and then project number.

Mind double and single quotes.

using “like” with cell to reference the last four digits in google sheets

1 Answers