Find Within Text String

[freybe06]

I apologize if this has been asked before - I did some searching and plugged in a few formulas but I couldn't get them to work...

I'm looking to write an if statement formula that will search within a cell's text string and if it finds "PAY THRU" or "PAY VIA" then (I should be able to write the rest from here). I'm just having a problem writing the find "pay thru" or "pay via" within a text string.

If anyone could help, I'd appreciate it.

Thanks!

[Ron Coderre]

Here's one approach...

Please Login or Register  to view this content.

EDITED TO INCLUDE THIS MORE COMPACT VERSION:

Please Login or Register  to view this content.

Is that something you can work with?

[freybe06]

Thank you Ron - I'm sorry but I left something out of my original question. I think I am going to need a sumproduct statement instead. How might I be able to plug in these if statements as one of the arrays?

Thanks again!

[Ron Coderre]

Instead of the "oh, one more thing" approach ...can you post what you *really* want to do? Then we'll do our best to help you get there.

[freybe06]

Will do -

I'm looking to write a sumproduct statement formula where the first array will search within a cell's text string and if it finds "PAY THRU" or "PAY VIA" (and I should be able to write out the rest of the arrays) then (I should be able to write the rest from here). I'm just having a problem writing the find "pay thru" or "pay via" within a text string.

Thanks!

[Ron Coderre]

Please post some sample data or a sample workbook and include the results you want to achieve...based on the sample data.

[freybe06]

Its kinda difficult - there are a lot of different variables and I can't post any samples online. But I'll try to explain this as best I can...

Tier 1
If column F equals column B, and if column G says "pay via" or "pay thru", then sum of column H where these rules apply. Otherwise, 0(zero).

Tier 2
If these rules apply and column E has an account number, then add Sheet2!K to the sum. (there are multiple account numbers, so I will have to match the account number in Column E to the same account in Sheet2!C.

Tier 3
If these rules apply and column F says "Virginia" then add column L to the sum.
If these rules apply and column F says "Georgia" then add column M to the sum.

[Ron Coderre]

This will definitely require a small mock-up workbook that contains the data structures (even if it's only fake data) you'll be using. Otherwise, I fear we'll all be heading off in different directions based on our individual interpretations of what you described.

[freybe06]

I think all I really need answered is how to use my original question as one of the rules/arrays in a sumproduct statement. I feel like I should be able to get the rest from there.

[freybe06]

Ron - Do you think you might be able to help me with that issue?

I'm looking to write a sumproduct statement formula where the first array will search within a cell's text string and if it finds "PAY THRU" or "PAY VIA" (and I should be able to write out the rest of the arrays) then (I should be able to write the rest from here). I'm just having a problem writing the find "pay thru" or "pay via" within a text string.

Thanks!

[Ron Coderre]

This formula section from post #2 tests if cell A1 contains "pay thru" or "pay via":

Please Login or Register  to view this content.

[freybe06]

I seem to be getting a #VALUE error. Instead of A1 I'm trying to use all of column G (Sheet1!$G$1:$G$10000) - would this be the cause of the error?

Thanks

[Ron Coderre]

We'll be happy to assist you but you need to post as many pertinent details as you can about your scenario.

[freybe06]

Here is the formula:

=SUMPRODUCT(--('Table 3 Text Files'!$C$1:$C$10000=1),--('Table 3 Text Files'!$F$1:$F$10000=B3),--(ISNUMBER(SEARCH(OR("VIA","THRU"),'Table 3 Text Files'!$G$1:$G$10000))),'Table 3 Text Files'!$I$1:$I$10000)

It works when I use:

=SUMPRODUCT(--('Table 3 Text Files'!$C$1:$C$10000=1),--('Table 3 Text Files'!$F$1:$F$10000=B4),--(ISNUMBER(SEARCH("VIA",'Table 3 Text Files'!$G$1:$G$10000))),'Table 3 Text Files'!$I$1:$I$10000)


The only difference is that I added OR("VIA","THRU") into the formula that worked. Now I get a zero for the formula.

Shouldn't the new formula be picking up anything in column G that has "VIA" or "THRU" ?

Thanks!

[Ron Coderre]

OR("VIA","THRU")...simply tests if either value equals TRUE.

There might be an easier way, but....
try this ARRAY FORMULA, completed by pressing CTRL+SHIFT+ENTER (instead of just ENTER)

Please Login or Register  to view this content.

Does that help?

[freybe06]

I am getting a #VALUE error for that formula...

Thanks

[freybe06]

I am getting a #VALUE error with that formula...

Thanks,

[Ron Coderre]

I am getting a #VALUE error for that formula...

Thanks

Using my best guess at what your data might consist of in my mock-up workbook, the formula does not return an error. Most likely, there are issues/errors in the referenced cells.