SUMIFS with Multple Criteria that point to a Cell

jaredj34

New Member
Joined
Mar 4, 2009
Messages
9
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,108
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 4, 2009
Messages
9
My issue is that the UnitCodes I what to sum change. Here is an example:

AB
AcctUnit Code to Sum
150100120,220,320,420,620
250200120,220,320,420,620,520
350300320
450400620

<tbody>
</tbody>

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

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,108
Office Version
  1. 365
Platform
  1. Windows
I may not have understood but maybe:
Code:
' =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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

New Member
Joined
Mar 4, 2009
Messages
9
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 50100120,220,320,420,620=SUM(SUMIFS(INDIRECT(B$1),Acct,$AB3,UC,{120,220,320,420,620}))
Overtime Pay 77,798 50200120,220,320,420,620,520=SUM(SUMIFS(INDIRECT(B$1),Acct,$AB4,UC,{120,220,320,420,620,520}))
Other Pay (Bonus) 11,501 50400320=SUM(SUMIFS(INDIRECT(B$1),Acct,$AB5,UC,{320}))
Sick/Vacation/Holiday Pay (PTO) (18,501)50600620=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

New Member
Joined
Mar 4, 2009
Messages
9

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Mar 4, 2009
Messages
9
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,644
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top