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

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


wrote in message
ps.com...
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

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
InsertNameDefine Codes Refers to
=Offset($A$2,0,0,COUNTA($A:$A)-1)
InsertNameDefine 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.
--
Regards

Roger Govier


wrote in message
ps.com...
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

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(accou nt,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.



wrote in message
ps.com...
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

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(a ccount,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.


wrote in message
ps.com...
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