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