Excel: Advanced Find Formula needed - R U a GURU? 3

[VBAjedi]

Hi!

I need one of you Excel gurus to help me out with a formula to put in Sheet2 column "B" that does the following:

Look in Sheet1 "MyRange" (multiple columns/rows) for all occurences of the alpha-numeric value located in Sheet2 column "A". Return the sum of the values in Sheet1 column "A" for all rows where a match was found.

E.g. if the value was found in Sheet1 "MyRange" cells B3 and C5, and if Sheet1 A3 and A5 contain "2" and "3" respectively, the formula would return "5".

Clear as mud?

Thanks!

VBAjedi

 

[Dreamboat]

=sumif(MyRange,"=A2",sheet1!A2:A65536)

or something like that.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[VBAjedi]

Anne,

I can't seem to get it to work. A much-simplified test seems to indicate that SUMIF will not evaluate a range that spans multiple columns. Any ideas?

Thanks,

VBAjedi

 

[DaleWatson123321]

VBAjedi,

I've created a working model that does exactly what you've described.

It uses database formulas, along with the following VBA routines.

It places the totals (from adding Column A of Sheet1) in column B of Sheet2, for each of the items in Column A of Sheet2, where the item in Column A is found in any of the columns in Sheet1. I've currently provided for 10 columns in Sheet1.

Sub Copy_Formulas()
'Copies each name in range named "CheckList" to cell named "key",
'then copies the result of the formula.
Application.ScreenUpdating = False
Set_CheckList
Application.Goto Reference:="R1C2"
For Each c In Range("CheckList&quot
c.Copy ("key&quot
Range("frm&quot.Copy
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
ActiveCell.Offset(1, 0).Select
Next
Application.CutCopyMode = False
Application.Goto Reference:="R1C1"
Application.ScreenUpdating = True
End Sub

Sub Set_CheckList()
'Creates name "CheckList"
Worksheets("Summary&quot.Select
Application.Goto Reference:="R1C1"
FirstCell = ActiveCell.Address
LastCell = [A65536].End(xlUp).Address
rng = FirstCell & ":" & LastCell
Range(rng).Name = "CheckList"
End Sub

If you'd like the file, email me and I'll send it via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[VBAjedi]

Dale,

I'll play with your solution tomorrow - I'm leaving now. For once, I would prefer a solution that uses an Excel formula (maybe like Dreamboats) without going into VBA. I don't want to be triggering a VBA routine every time my data changes.

But I don't know that that will be possible. . . DreamBoats is close, though! I just need it to review multiple (12) columns for matches.

VBAjedi

 

[Dreamboat]

Nope. I don't think it's happening, jedi. Tried every which way. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[GlennUK]

Something like this array formula will do what you want ...

=SUM(IF(MMULT(IF(MyRange=A1,1,0),{1;1;1})=1,Sheet1!$A$1:$A$12,0))

entered by CTRL-SHIFT-ENTER instead of ENTER.

Change {1;1;1} to be however many 1's ( separated by ; ) as there are columns in the range MyRange. And also change the Sheet1!$A$1:$A$12 range to be as long a required.

Cheers, Glenn.

 

[xlbo]

Bit complex that Glen - wouldn't this do ?
=SUM((myRange="a&quot*(INDIRECT("Sheet1!A1:A"&ROWS(mRange))))
entered with CTRL+SHIFT+ENTER

The only kicker is that mYrange MUST start in row 1 - otherwise you will need to hardcode the number of rows down it is
=SUM((mRange="a&quot*(INDIRECT("A10:A"&ROWS(mRange)+10))) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[GlennUK]

Hey yes Geoff, that's simpler.

But that kicker about the start row is dealt with if you use the OFFSET function like this ...


=SUM((MyRange="a&quot*(OFFSET(MyRange,0,-1,,1)))

with CTRL-SHIFT-ENTER.

Glenn.

 

[xlbo]

veeerrry nice Glen - just to clarify tho - this would work if col A is NOT in myRange
If col A IS within myRange, there will be a Ref! error
If col A IS within myRange, it'd need to be changed to:
=SUM((MyRange="a&quot*(OFFSET(MyRange,0,0,,1))) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[VBAjedi]

Wow! Y'all been busy in here! And there's some promising suggestions posted as well.

However, I'm having a bit of trouble applying these formulas because I'm not always sure what ranges you're referring to. I have TWO column "A"'s in play: Sheet2 column "A" contains a list of names I want to find in Sheet1's "MyRange", while Sheet1 column "A" contains a list of "rank/value scores" to be summed (summed once for each match being found in that row). For clarification I have named these two ranges "FindValues" and "ScoreValues" respectively.

Basically I am trying to compile a total score for each person by adding the "ScoreValues" scores for all corresponding "MyRange" rows in which that persons name appears (and add that row's score twice if their name appears twice in that row).

So can someone kindly explain how these formulas do that?

Many thanks for all your thoughts!

VBAjedi

 

[VBAjedi]

Based on the above input, as well as article #267982 on support.Microsoft.com, I got my solution. The key was to use Sum with a NESTED If statement (instead of SUMIF, which only looks at one column), as follows:

SUM(IF(MyRange=A6,ScoresRange))

where A6 contains the value I want to search for in MyRange. It works perfectly! Incidentally, the above Microsoft article also demonstrates how to Sum values from a range only when another range meets MULTIPLE criteria. Very interesting!

Thanks all! A round of stars for your thoughts.

VBAjedi