sumif with condition

[VegasL]

Hi,

I'm trying to do a sumifs with a condition. See attached file. Tab 1 raw data. Tab 2 expected result


Tab 2:

Column B = should be sum if (column a & column c match). However also, if value in column c contains "truck" or "trucks - toyota" etc, then do a sum if.

See 2nd Tab for expected outcome.

[Special-K]

1. Column A in both spreadsheets is "Accts"
Column C in Tab 1 is "Cost", Column C in Tab 2 is "Type"

Therefore column a and c will NEVER match.
They're three different things.

2. What does "etc" mean ? It means NOTHING to Excel.
Provide a list of what the Type should be
"truck" or "trucks - toyota" or..........................?

[BadlySpelledBuoy]

Try this in B2 on Tab2 and copy down.

Please Login or Register  to view this content.

Not sure how you arrived at 350 as an expected figure for ABC123 / Truck... I make it 300, unless I've misunderstood the requirement.

BSB

[VegasL]

Yes this worked perfectly. Thanks very much - Appreciated. I assume left (c2,3)&"*"...is saying in plain english...in cell c2, count first 3 characters and use that as the root value to match & anything that comes after it hence &"*", also count those values, correct?

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[BadlySpelledBuoy]

No problem, happy to help.

I assume left (c2,3)&"*"...is saying in plain english...in cell c2, count first 3 characters and use that as the root value to match & anything that comes after it hence &"*", also count those values, correct?

Correct indeed. It's looking for the first 3 characters in C2, adding a wildcard and then searching for any match for that in ColB on Tab1.
It's not a perfect solution in all situations, but it works for the sample data you provided.

BSB