Sum Product, SumIF, or Both?

[BobA]

=SUMIF('Tom (2)'!Y249:Y300,"(gs)",'Tom(2)'!W249:W300)

I have this formula in a cell, which works fine if I have just(GS)in the Y column.

If I have anything else it doesn't work. Such as, 4 to (GS, or (MP)(GS), etc.

What I want to do is add up all the money in the W column for all instances of (GS) in the Y column...whether there is anything else in the Y column or not.

How do I do that?

Thanks

 

[Claus Busch]

Hi Bob,

Am Tue, 2 Jul 2013 05:58:38 -0700 (PDT) schrieb BobA:

=SUMIF('Tom (2)'!Y249:Y300,"(gs)",'Tom(2)'!W249:W300)

I have this formula in a cell, which works fine if I have just(GS)in the Y column.

If I have anything else it doesn't work. Such as, 4 to (GS, or (MP)(GS), etc.

What I want to do is add up all the money in the W column for all instances of (GS) in the Y column...whether there is anything else in the Y column or not.

try:
=SUMIF('Tom(2)'!Y49:Y300,"*GS*",'Tom(2)'!W49:W300)


Regards
Claus B.

 

[BobA]

Thank you, Claus!

Since that was so easy, one more?

What if in the W column I have $10.00/$20.00 (or $10.00,$20.00), and in the Y column I have (GS)(MP).

How do I write the formula so that just the $10.00 corresponds with the (GS)?

 

[Claus Busch]

Hi Bob,

Am Tue, 2 Jul 2013 06:19:45 -0700 (PDT) schrieb BobA:

What if in the W column I have $10.00/$20.00 (or $10.00,$20.00), and in the Y column I have (GS)(MP).

How do I write the formula so that just the $10.00 corresponds with the (GS)?

insert a empty column right of column W=> TextToColumns => Delimited =>
Delimiter = Comma and Slashand refer to the new column in the formula


Regards
Claus B.

 

[BobA]

Thanks very much, Claus.

 

[Claus Busch]

Hi Bob,

you can also insert the following function and call it into the sheet
with
=mySum('Tom(2)'!W49:W300,"GS")

Function mySum(myRng As Range, SStr As String) As Double
Dim rngC As Range
Dim myVal As Variant

For Each rngC In myRng
myVal = IIf(InStr(rngC, "$") > 0, Left(Replace(rngC, _
"$", ""), InStr(myVal, ",") + 2), rngC)
If InStr(rngC.Offset(, 2), SStr) > 0 Then
mySum = mySum + myVal
End If
Next
End Function


Regards
Claus B.

 

[BobA]

This one is a little above my pay grade, but I'll play around with it nonetheless. Thanks again.