Sumif using partial string

[tcebob]

Win 10, Excel 2003

I download a .csv file from my bank account and import it to an Excel spreadsheet. I can then create a new column showing only those transactions containing "Publix, using the formula: =IF(ISNUMBER(FIND("PUBLIX",$C34)),$D34,""). Then I can sum the column to find how much I spend at Publix. I believe this process can be condensed into one statement using ISNUMBER but I can't get it right. My best effort: =SUMIF($C33:$C79,(isnumber(find "publix")),$D33:$D79), where the store data is in col. C and the amounts in col. D.

 

[Amy @ German Pearls]

If I'm understanding this correctly you could use a SUMIF function. You would just write =sumif(c:c,"Publix",d:d)
This would some the corresponding values in D if that row also had Publix in column C.
Hope this helps.
Amy

 

[Becky]

Agreed, SUMIF should do the job

 

[tcebob]

Thanks, Amy. Your suggestion would work if "Publix" were the only word in the range. Here's the actual text:
"PUBLIX SUPER M 02/17 #000950936 PURCHASE PUBLIX SUPER MAR SUNRISE FL"
I need to isolate the word "Publix" because all entries are different by date and serial number. The formula "=IF(ISNUMBER(FIND("PUBLIX",$C34)),$D34,"")" does that for each line, making a column, but I don't know how to incorporate ISNUMBER into the SUMIF formula.

Bob

 

[Becky]

I think you need to create a column to test whether the cell contains "PUBLIX" - this would be on a cell by cell basis using ISNUMBER - for example =ISNUMBER(SEARCH("PUBLIX",A1)) would return TRUE if A1 contains PUBLIX. Run this formula down the column and you would have a column of nothing but TRUE and FALSE.

Then your SUMIF would work from this data. It would simply be =SUMIF([range of data containing the TRUE and FALSE results],"TRUE",[range of data with the numerical values])

 

[Amy @ German Pearls]

What about:

=SUMIF(A:A, "*publix*",B:B)

This should total the B column if Publix is anywhere in the first column's text. The quotes surround the string and the * on either side indicates any characters before or after the letters publix.

 

[tcebob]

Amy, that did it!. Thanks for your help.

Bob

 

[Amy @ German Pearls]

Amy, that did it!. Thanks for your help.

Bob

Awesome! Your welcome!
Amy