Dsum or SumIf

[McNamara.BrianT]

I'm trying to keep a running total (in one cell) for a range of cells
(see below).

The cells in the range consist of two columns- account number and an
amount. Given that the account numbers range from those beginning with
2XXX, 3XXX and 4XXX. The running total should be derived from the
totals associated to accounts beginning with 2XXX and 4XXX.

First attempt using pivot tables doesn't work since the amount column
is dynamic and thus is changed by user input - pivot tables would need
to be refreshed regularly which isn't possible. My goal is to have
this running total also be dynamic and performed automatically.

Second attempt using SUMIf doesn't work because the 'criteria' also
changes and isn't statis; the criteria in this case would be moving
with each row of data.

Third attempt using DSUM doesn't work b/c I can't seem to get the
syntax correct.

Can anyone share any input that might assist?

Account Original Budget
535030 24,600.00
535030 -
535036 36,900.00
535037 -
247695 -
258804 17,936.10
520712 -
520799 22,420.13
533842 44,840.25
533843 -
247516 -
247695 -
258804 30,672.69
258827 46,009.04
539571 55,024.04
535030 18,655.00
535030 -
535036 9,327.50
535037 -
536220 -
565525 -
589039 46,637.50
258804 20,723.50


Thanks.

-Brian

 

[Bernie Deitrick]

Brian,

If A is formatted as text (string values)
=SUMIF(A:A,"2*",B:B)+SUMIF(A:A,"4*",B:B)

If A is numbers, and always 6 digits long:
=SUMIF(A:A,">=200000",B:B)-SUMIF(A:A,">=300000",B:B)+SUMIF(A:A,">=400000",B:B)-SUMIF(A:A,">=500000",B:B)

HTH,
Bernie
MS Excel MVP

 

[Roger Govier]

Hi

One way
=SUMPRODUCT(--(LEFT($A$2:$A$1000)="2")+(LEFT($A$2:$A$1000)="4"),$B$2:$B$1000)
Note Sumproduct cannot take whole columns as ranges.

To deal with growing data ranges I would define 2 named ranges
Insert>Name>Define> Codes Refers to
=Offset($A$2,0,0,COUNTA($A:$A)-1)
Insert>Name>Define> Amount Refers to
=Offset($B$2,0,0,COUNTA($B:$B)-1)

The formula then becomes
=SUMPRODUCT(--(LEFT(codes)="2")+(LEFT(codes)="4"),amount)

Equally, if you defined a named range called Data as
=Offset($A$1,0,0,COUNTA($A:$A),2)
and used this as the range for your Pivot Table you could have used
Pivot Tables to resolve the problem.

 

[Bob Davison]

Here is a way to easily change the sum criteria with wilcard-like inputs:



Named Ranges:



Column array containing the account numbers = "account"



Column array containing the amounts = "amount"



Single cell for first wildcard input = "Wild1"



Single cell for second wildcard input = "Wild2"



Single cell containing formula LEN(Wild1) = "Len1"



Single cell containing formula LEN(Wild2) = "Len2"





This formula will result in the sum of the accounts beginning with the Wild1
or Wild2 inputs:



=SUMPRODUCT(--(LEFT(account,Len1)=TEXT(Wild1,"@"))+(LEFT(account,Len2)=TEXT(Wild2,"@")),amount)



This formula will result in the sum of the accounts ending with the Wild1 or
Wild2 inputs:



=SUMPRODUCT(--(RIGHT(account,Len1)=TEXT(Wild1,"@"))+(RIGHT(account,Len2)=TEXT(Wild2,"@")),amount)



This will work with Wild inputs other than numbers as well.



To get the specific results you need, put a 2 in the Wild1 cell and a 4 in
the Wild2 cell.

 

[Bob Davison]

further simplified:

delete the Len1 and Len2 named ranges from the above post and use the
following formulas:

first charater(s) match:
SUMPRODUCT(--(LEFT(account,LEN(Wild1))=TEXT(Wild1,"@"))+(LEFT(account,LEN(Wild2))=TEXT(Wild2,"@")),amount)

last character(s) match:
=SUMPRODUCT(--(RIGHT(account,LEN(Wild1))=TEXT(Wild1,"@"))+(RIGHT(account,LEN(Wild2))=TEXT(Wild2,"@")),amount)

note this allows a multiple character criteria sum...can find an exact
account for example.