Columns E and F. (Text is all in one cell in column E)

AA BB CC 1
QQ WW EE 2
AA SS DD 3
AA BB CC 4
QQ WW EE 5
AA SS DD 6
AA BB CC 7

=SUMIF(E1:E7,"AA*",F1:F7) returns 21

How do I sumif for the cells that have AA & CC which would return 12?

Howard

Hi Howard,

Am Fri, 13 Feb 2015 09:03:25 -0800 (PST) schrieb L. Howard:

Columns E and F. (Text is all in one cell in column E)

AA BB CC 1
QQ WW EE 2
AA SS DD 3
AA BB CC 4
QQ WW EE 5
AA SS DD 6
AA BB CC 7
How do I sumif for the cells that have AA & CC which would return 12?

try:
=SUMIF(E1:E7,"AA*CC",F1:F7)
=SUMIFS(F1:F7,E1:E7,"AA*",E1:E7,"*CC")



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Friday, February 13, 2015 at 9:13:33 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Fri, 13 Feb 2015 09:03:25 -0800 (PST) schrieb L. Howard:

Columns E and F. (Text is all in one cell in column E)

AA BB CC 1
QQ WW EE 2
AA SS DD 3
AA BB CC 4
QQ WW EE 5
AA SS DD 6
AA BB CC 7
How do I sumif for the cells that have AA & CC which would return 12?

try:
=SUMIF(E1:E7,"AA*CC",F1:F7)
=SUMIFS(F1:F7,E1:E7,"AA*",E1:E7,"*CC")



Regards
Claus B.

Interesting. Would have never figured that out and I am suprised how little I could find on how to look for two "XX"'s in a cell.

I suppose one could expand to three...?

I'll play with that to see.

Thanks Claus.

Howard

Hi Howard,

Am Fri, 13 Feb 2015 11:53:14 -0800 (PST) schrieb L. Howard:

I suppose one could expand to three...?

if you have 3 A the formula takes it as correct because AAA contains AA.

Try it with a UDF:

Function mySum(myRng As Range) As Double
Dim varData As Variant
Dim i As Long

varData = myRng
For i = 1 To UBound(varData)
If InStr(varData(i, 1), "AA") And InStr(varData(i, 1), "CC") Then
mySum = mySum + varData(i, 2)
End If
Next
End Function

And call this function in the sheet with
=mySum(E1:F7)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

:

I suppose one could expand to three...?

if you have 3 A the formula takes it as correct because AAA contains AA.

Try it with a UDF:

Function mySum(myRng As Range) As Double
Dim varData As Variant
Dim i As Long

varData = myRng
For i = 1 To UBound(varData)
If InStr(varData(i, 1), "AA") And InStr(varData(i, 1), "CC") Then
mySum = mySum + varData(i, 2)
End If
Next
End Function

And call this function in the sheet with
=mySum(E1:F7)



That's good to have, the UDF, I'll for sure use that.

I was actually thinking of how to look for AA BB & CC words in a single cell where the entire contents of the cell would be something like...

AA XX CC VVV BB RRRR

and really wonder how useful that would ever be. Probably not worth pursuing, I really don't have a need at hand for that, just curious.

I tried to reference cell values using the formulas you offered, where the cells would each hold a CC and a BB etc. but it did not work. Returned 0.

Howard

It just occurred to me that one could use cell references to write the BB CC etc. to the UDF as a variable.

I'll go try that.

Howard

Hi Howard,

Am Fri, 13 Feb 2015 14:32:08 -0800 (PST) schrieb L. Howard:

It just occurred to me that one could use cell references to write the BB CC etc. to the UDF as a variable.

then you have to enlarge the declaration.

But here a more reliable UDF for a string with 3 times 2 characters.
If you write the first substring in A1 and the second in A2 then call
the UDF with:
=mysum(E1:F7;A1;A2)

Function mySum(myRng As Range, str1 As Range, _
str2 As Range) As Double
Dim varData As Variant
Dim n As Long
Dim myStr As String

varData = myRng
For n = 1 To UBound(varData)
myStr = Replace(Replace(varData(n, 1), str1, ""), str2, "")
If Len(Trim(myStr)) = 2 Then
mySum = mySum + varData(n, 2)
End If
Next
End Function



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Sat, 14 Feb 2015 09:45:42 +0100 schrieb Claus Busch:

=mysum(E1:F7;A1;A2)

or for all length of a string:

Function mySum(myRng As Range, str1 As Range, _
str2 As Range) As Double
Dim varData As Variant
Dim n As Long
Dim myStr As String
Dim lenSub As Long

varData = myRng
lenSub = Len(str1) + Len(str2) + 2
For n = 1 To UBound(varData)
myStr = Replace(Replace(varData(n, 1), str1, ""), str2, "")
If Len(Trim(myStr)) = Len(varData(n, 1)) - lenSub Then
mySum = mySum + varData(n, 2)
End If
Next
End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Saturday, February 14, 2015 at 1:03:33 AM UTC-8, Claus Busch wrote:
Hi again,

Am Sat, 14 Feb 2015 09:45:42 +0100 schrieb Claus Busch:

=mysum(E1:F7;A1;A2)

or for all length of a string:

Function mySum(myRng As Range, str1 As Range, _
str2 As Range) As Double
Dim varData As Variant
Dim n As Long
Dim myStr As String
Dim lenSub As Long

varData = myRng
lenSub = Len(str1) + Len(str2) + 2
For n = 1 To UBound(varData)
myStr = Replace(Replace(varData(n, 1), str1, ""), str2, "")
If Len(Trim(myStr)) = Len(varData(n, 1)) - lenSub Then
mySum = mySum + varData(n, 2)
End If
Next
End Function


Regards
Claus B.



Thanks Claus, for both the UDFunctions

Howard

Hi Howard,

Am Sat, 14 Feb 2015 02:00:30 -0800 (PST) schrieb L. Howard:

If Len(Trim(myStr)) = Len(varData(n, 1)) - lenSub Then

VBA Trim does not work as expected. I changed it to
WorksheetFunction.Trim. That is necessary if your strings are anywhere
into the string:

Function mySum(myRng As Range, str1 As String, _
str2 As String) As Double
Dim varData As Variant
Dim n As Long
Dim myStr As String
Dim lenSub As Long

varData = myRng
lenSub = Len(str1) + Len(str2) + 2
For n = 1 To UBound(varData)
myStr = Replace(Replace(varData(n, 1), str1, ""), str2, "")
If Len(WorksheetFunction.Trim(myStr)) = _
Len(varData(n, 1)) - lenSub Then
mySum = mySum + varData(n, 2)
End If
Next
End Function


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional