SUMIFS with Multple Criteria that point to a Cell

[jaredj34]

Not real sure how to word this but I was able to create (thanks to posts on this site) the following formula.

=SUM(SUMIFS(January,Acct,$AE54,UnitCode,{120,220,320,420,620}))

Now I would like to take one step further. I would like to replace the {120,220,320,420,620} with a cell reference of the same information. I would like to avoid add an array of static values into 1000s of formulas.

Any Ideas?

Jared

 

[Kenneth Hobson]

Well if you do it, then does it work? Another option would be to name a range and reference that. Otherwise, you can use a macro to rebuild the formulas I guess.

 

[jaredj34]

My issue is that the UnitCodes I what to sum change. Here is an example:

	A		B
	Acct		Unit Code to Sum
1	50100		120,220,320,420,620
2	50200		120,220,320,420,620,520
3	50300		320
4	50400		620

<tbody>
</tbody>

So I would like a way to enter B1 and a reference instead of adding {120,220,320,420} to each formula.

 

[Kenneth Hobson]

I may not have understood but maybe:

' =splitsum(A1) where A1 might equal: '120,220,320,420,620 summed to 1700.
Function SplitSum(aRange As Range) As Double
  Dim v As Variant, d As Double
  For Each v In Split(aRange, ",")
    d = d + CDbl(v)
  Next v
  SplitSum = d
End Function

 

[Aladin Akyurek]

Not real sure how to word this but I was able to create (thanks to posts on this site) the following formula.

=SUM(SUMIFS(January,Acct,$AE54,UnitCode,{120,220,320,420,620}))

Now I would like to take one step further. I would like to replace the {120,220,320,420,620} with a cell reference of the same information. I would like to avoid add an array of static values into 1000s of formulas.

Any Ideas?

Jared

My issue is that the UnitCodes I what to sum change. Here is an example:

	A		B
	Acct		Unit Code to Sum
1	50100		120,220,320,420,620
2	50200		120,220,320,420,620,520
3	50300		320
4	50400		620

<TBODY>
</TBODY>

So I would like a way to enter B1 and a reference instead of adding {120,220,320,420} to each formula.

=SUMPRODUCT(SUMIFS(January,Acct,$A1,UnitCode,EVAL($B1)))

For this formula to work, you need to add EVAL to your workbook, using Alt+F11...

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

 

[jaredj34]

Thanks for the help but I am trying to avoid VB if possible. Also I would like to be able to see what Unit Code are summed and I would like to be able to change which Unit Codes are summed for a particular Account on the fly. Here is a better table of want I have so far.

	January
	2013 Actuals	Account	Unit Code 1	Formula
SUTA Tax	1,087,194	50100	120,220,320,420,620	=SUM(SUMIFS(INDIRECT(B$1),Acct,$AB3,UC,{120,220,320,420,620}))
Overtime Pay	77,798	50200	120,220,320,420,620,520	=SUM(SUMIFS(INDIRECT(B$1),Acct,$AB4,UC,{120,220,320,420,620,520}))
Other Pay (Bonus)	11,501	50400	320	=SUM(SUMIFS(INDIRECT(B$1),Acct,$AB5,UC,{320}))
Sick/Vacation/Holiday Pay (PTO)	(18,501)	50600	620	=SUM(SUMIFS(INDIRECT(B$1),Acct,$AB6,UC,{620}))

<tbody>
</tbody>

There is a data worksheet that has many more accounts and unit codes. I am attempting to find a way to consolidate the sum of many unit codes into 1 account/row. I basically would like way to take the contents out of the Unit Code Column and replace the bracketed {} portion of the Formula.

 

[jaredj34]

Aladin,

Thanks for the code. I have to admit, I have not played with adding code like this. I was able to add the EVAL Module but it returned 0.

=SUMPRODUCT(SUMIFS(INDIRECT(B$5),Acct,Q47,UC,EVAL($R47))) The Indirect Function converts the Month to point to a Range Name. $A1 is the account number I wanted summed and $R47 is the list of Unit Codes I wanted summed for the related Account. Could the Data in $R47 be formatted incorrectly?

 

[Aladin Akyurek]

Aladin,

Thanks for the code. I have to admit, I have not played with adding code like this. I was able to add the EVAL Module but it returned 0.

=SUMPRODUCT(SUMIFS(INDIRECT(B$5),Acct,Q47,UC,EVAL($R47))) The Indirect Function converts the Month to point to a Range Name. $A1 is the account number I wanted summed and $R47 is the list of Unit Codes I wanted summed for the related Account. Could the Data in $R47 be formatted incorrectly?

What do you have in B5?

 

[jaredj34]

Aladin,

I got it!!! The Unit Codes Field/Cell does not have the Brackets {}. I added a Concatenate and it works. I have no idea what the code you wrote does but it works.

=SUMPRODUCT(SUMIFS(INDIRECT(B$5),Acct,$Q52,UC,EVAL(CONCATENATE("{",$R52,"}"))))

Aladin and Ken, Thank you for the help.


Jared

 

[Aladin Akyurek]

Aladin,

I got it!!! The Unit Codes Field/Cell does not have the Brackets {}. I added a Concatenate and it works. I have no idea what the code you wrote does but it works.

=SUMPRODUCT(SUMIFS(INDIRECT(B$5),Acct,$Q52,UC,EVAL(CONCATENATE("{",$R52,"}"))))

Aladin and Ken, Thank you for the help.


Jared

Great. Thanks for providing feedback.