CODEWITHSUNDEEP
google-sheets
wroj
wroj

Reputation: 1

Can I use COUNTIF to count text strings in a range that exist within, or match, a single cell?

For this question I have made a simplifed example spreadsheet to illustrate what I am trying to achieve.

This can be found here.

https://docs.google.com/spreadsheets/d/1qbzuDHfRnGHkDhrcQWbQxRVcxsqo3MlJ3pZtU16nq7w/edit?usp=sharing

I will give background on the sheet first and then explain the problem, Forgive me if it's a bit convoluted it's my first time asking a question on here.

In the left hand side table I have example bank transactions made. In the right hand side table I have example data from my cash flow forecast. The goal is to match bank references and amounts to make sure that the forecast and the real transactions match up.

Actual Bank Transaction Table on Left Hand Side

Forecast Transactions on Right Hand Side

The reason I am doing it with counting ref and price matches is that I then use these to automatically decide whether the transaction is a match to the forecast or not.

The problem is how to achieve my formula for Column D of my example spreadsheet. I need to check for price matches between each cell in column D and the range G3:G16 but only count matches where the transaction reference in column A matches to the range F3:F16 as per column C. This is to cut out many price matches that are not relevant.

The formula in C3 is currently

=if(A3="","",SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$16,A3))*($F$3:$F$16<>"")))

This formula works great and is able to look at the range F3:F:16 and count both an exact match or strings contained within the string in Column A (i.e. Col A = "NETFLIX 23424589745897872134" and Col F = "NETFLIX"). This formula essentially allows me to count text strings in a range that exist within, or match, a single cell.

So at this point I cannot work out what the formula needs to be in Column D. I have tried to create a COUNTIFS formula with wildcards "" & & "" but I can't make anything work. My problem is that I can't utilise the current formula in Column C within a COUNTIFS in column D. Is there a way I can achieve the same outcome of Column C in a COUNTIF formula? Then I would be able to use that within a COUNTIFS formula in column D and my problem is solved... Hooray!

I know there must be a way but so far gogle has not been able to help me find the answer.

Please help!

Thank you :-)

Upvotes: 0

Views: 330

Answers (1)

player0
player0

Reputation: 1

in D3 try:

=INDEX(IFNA(VLOOKUP(REGEXEXTRACT(A3:A, TEXTJOIN("|", 1, F3:F)),  F:G, 2, 0)))

update 1:

=INDEX(IF(A3:A="",,(B3:B=IFNA(VLOOKUP(REGEXEXTRACT(A3:A, 
 TEXTJOIN("|", 1, F3:F)),  F:G, 2, 0)))*1))

enter image description here

update 2:

=INDEX(IF(A3:A="",,
 IFNA(VLOOKUP(REGEXEXTRACT(A3:A, TEXTJOIN("|", 1, F3:F)),  QUERY({F3:F}, 
 "select Col1,count(Col1) group by Col1"), 2, ), 0)))

enter image description here

Upvotes: 0

Related Questions