sum + sumifs on multiple columns to produce unique results

[lampropeltis]

I have four columns of data in my sample set (A, B, C, D)

Data set:
a b c d
1 Country MW Company Parent

2 USA 10 GDF GDF
3 Mexico 20 Mitsui (blank)
4 Brazil 30 (blank) Petrobras

Results

5 Country Company MW

6 USA GDF 10
7 Mexico Mitsui 0 is returned but it should be 20
8 Brazil Petrobras 0 is returned but it sould be 30

The (incorrect, but working) formula in C6, C7, C8... is
=SUMIFS(b1:b3, a1:a3, b7, C1:C3, "*mitsui*", d1:d3, "*mitsui*")
unfortunately this formula counts the MW only if BOTH conditions exist (if Mitsui is in both Company and Parent) but I want the formula to account for the company if it is in EITHER parent OR company.

I have tried sum(sumifs.... but that ADDED the two columns, basically giving me 20MW for GDF, because their name happens to be in both parent and company - but i only want it counted once, for obvious reasons.

Thanks for any help or suggestions.
(sorry, the post didnt keep my spaces for the data & results, so they are no longer lined up)

And if someone can link me a thread on how to post actual data or a link to it - or an image (screenshot), I would appreciate it too. I did read the stickies and didnt see it described in there.

 

[Jonmo1]

Try adding a helper column in column E for example.
E1 and filled down: =C1&"|"&D1

Then use
=SUMIFS(b1:b3, a1:a3, b7, E1:E3, "*mitsui*")

 

[XOR LX]

Or:

=SUM(SUMIFS(B:B,A:A,B7,C:C,{"=","=","<>"}&"*mitsui*",D:D,{"=","<>","="}&"*mitsui*"))

Regards

 

[lampropeltis]

those both worked wonderfully.

I elected to go with Xor's technique (admittedly having no clue how the mechanics of it are actually functioning) because it did not require me to manipulate the data set by adding the helper row.

However, I completely understand how Jonmo did his, and it makes perfect sense. I tested it out and learned something new about combining columns.

Thanks all!
-J

 

[XOR LX]

You're welcome.

It's equivalent to performing each of:

=SUMIFS(B:B,A:A,B7,C:C,"*mitsui*",D:D,"*mitsui*")

=SUMIFS(B:B,A:A,B7,C:C,"*mitsui*",D:D,"<>*mitsui*")

=SUMIFS(B:B,A:A,B7,C:C,"<>*mitsui*",D:D,"*mitsui*")

i.e. each of the three mutually-exclusive events which could account for "mitsui" occurring in at least one of those columns, and then summing the results.

Regards

 

[lampropeltis]

Got it! Thanks Xor.
I will be checking out the advanced excel techniques page as well.

-J

 

[XOR LX]

Funnily enough I was just in the middle of writing a post on this very topic...

Cheers!