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