Any Excel junkies in here? Function question

[Mike]

Okay, I've got a spreadsheet, and I want to add a column based on if certain text appears in another column.

Sounds simple enough. Just use SUMIF, right?

Here's the problem:

Cell $B960 (I want the column to stay the same when copied, but the row can change) contains a portion of what it needs to look for.

Think of it this way:

The cells you see with letters could be last names. Above are bunch of first and last name combos. I want to add up the impressions, clicks, etc of the Smiths, Jones, Wallaces, etc.

So, my thinking is that the formula would look like this:

=SUMIF($B$2:$B$931,"*-"$B960,C$2:C$931)

The yellow part is the wildcard part, and the green being the cell reference.

That doesn't work!

I'm going nuts trying to figure this out.

Help!

 

[rusvik]

Bro, I just felt stupid or you didn't explain it well enough.

Ok, so I think I get what you want, you want to check for common words in ads and then sum it? That is, look for the last names and then add them based on that criteria?

First of all, why not just write a short macro to do this. Load the ads/names into an array, separate them with some left/right stuff and looking for blank spaces, then create a loop to add all the numbers together?

 

[Wright]

It has been awhile, but if you're adding operators (or in this case a wildcard) to the cell reference, don't they have to be within " "?

This might work:

=SUMIF($B$2:$B$931,""*-"$B960",C$2:C$931)

 

[Mike]

I don't know how to do macros.

Guess it's time to learn.

Explaining what I'm trying to do:

Add cells together from column C, but only if the corresponding cell in column B matches the snippet of text in cell B960.

Did that make more sense?

 

[Mike]

It has been awhile, but if you're adding operators (or in this case a wildcard) to the cell reference, don't they have to be within " "?

This might work:

=SUMIF($B$2:$B$931,""*-"$B960",C$2:C$931)

Didn't work.

The criteria in words:

Anything can appear before (*) the hyphen (not a negative sign), but has to end with the content of the aforementioned cell.

So, if Cell B960 = ABC

I want it to be able to match:

123-ABC
52556-ABC
KKDHH-ABC

Get it?

 

[dreamache]

Thanks for that high school computer class flashback nightmare.

 

[rusvik]

Didn't work.

The criteria in words:

Anything can appear before (*) the hyphen (not a negative sign), but has to end with the content of the aforementioned cell.

So, if Cell B960 = ABC

I want it to be able to match:

123-ABC
52556-ABC
KKDHH-ABC

Get it?

Ok, yes, but you need to tell me if this is a specific format or not? Is every word you want to lookup separated by a "-".

 

[Mike]

Yes. Every one is separated by a hyphen.

All the data that it's looking at has something then a hyphen then ends with the value I have in B960

 

[microphone head]

Not sure, but maybe something like this?

FIND( substring, string, [start_position] )



Found some reference that may help:

MS Excel: FIND Function (WS)


I'm out of here - Beer time!

 

[momchil9]

pro hint: you are missing a parenthesis somewhere

 

[rusvik]

Yes. Every one is separated by a hyphen.

All the data that it's looking at has something then a hyphen then ends with the value I have in B960

First to get the last part: =right(Cell,find("-",Cell,1),-1), I think that should get you the last part after the hyphen. So just add another column and drag and drop, then use the sumif straightforward. If it doesn't work then I'll take another look tomorrow.

Also, try asking the question at one of the many excel forums. They're usually excellent-

 

[spitfire]

You would be better using Data -> Text to Columns -> Delimited by "-" and matching the new column that way.

However this should also work:

=SUMIF(
$B$2:$B$931,
RIGHT($B960,(LEN($B960)-FIND("-",$B960))),
C$2:C$931)

Any good?

 

[greenleaves]

When I do complex shit in excel I break it down. So I'll have a hidden column that will calculate say, if x matches y. Then on another one, take that information and do abc with it.

I know there are better ways, but, from my experience, when trying to hack shit in excel, take it 1 step at a time. Latter you can always consolidate.

 

[Mike]

pro hint: you are missing a parenthesis somewhere

Okay I give up. Where?

 

[momchil9]

try that

=SUM(IF($B$2:$B$931,"*-"$B960,C$2:C$931))

 

[Mike]

=SUMIF($B$2:$B$931,"*-ABC",C$2:C$931)

Works perfectly, but is very very tedious changing ABC in 5 different cells on one line, when you have more than 50 lines.

I want to make ABC dynamic based on the contents of a cell (in the original example, cell B960).

 

[Kiopa_Matt]

Excel doesn't have any regex support? That sucks.

Learn to program. PHP is easy, would make problems like this disappear, and would probably open new possibilities for you.

 

[spitfire]

=SUMIF($B$2:$B$931,"*-" & B1 ,C$2:C$931)

Where B1 is whatever cell "ABC" is in.

Does that work?

 

[Mike]

=SUMIF($B$2:$B$931,"*-" & B1 ,C$2:C$931)

Where B1 is whatever cell "ABC" is in.

Does that work?

Winner winner chicken dinner! Thank you!

Ampersand makes the world go round. Perfect!