Sum on a wildcard???

[dakotasteve]

My Dilemma

I need a way to sum only the salary accounts (they begin with 511XXX)
especially summarizing all

Program Description Account # Amount

2010 Salaries 511200 100
2010 Call Back Pay 511202
120
2010 Utilities 530004
400
2020 Overtime 511201 150
2030 Benefits 52000
200
2040 Periodicals 53000 250
3010 Salaries 511200
300
3010 Benefits 52000
350
4010 Salaries 511200
400

My goal is to create a report that would cherry pick a report tha
would give me a summary of all 511XXX salary accounts totaled b
program. In this example I would expect to see Program 2010 to tota
to $220. There are approximately 30 accounts that begin with the 51
prefix, so I think I need to use a wildcard to sum

 

[Max]

One way ..

Assume the source data is in Sheet1, cols A to D,
data from row2 down
(col A = Program, col C = Account#, col D = Amount)

In Sheet2
-------------
Put in A1: 511
(i.e. the acct type)

Create the table below in cols A and B,
with labels "Progr" and "Amt" in A2:B2

Progr Amt
2010 ?
2020 ?
2030 ?
etc

Put in B3:

=SUMPRODUCT((Sheet1!$A$2:$A$10=A3)*(LEFT(Sheet1!$C$2:$C$10,3)+0=$A$1),Sheet1
!$D$2:$D$10)

Copy B3 down

Col B will return the Program amounts for Acc type 511xxxx

Adjust the ranges to suit ..

 

[RagDyer]

With your data list in A1 to D100,
And labels in Row1,
Enter the Program to sum into E1,
And this formula into E2:

=SUMPRODUCT((A2:A100=E1)*(ISNUMBER(FIND("511",C2:C100)))*D2100)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



My Dilemma

I need a way to sum only the salary accounts (they begin with 511XXX),
especially summarizing all

Program Description Account # Amount

2010 Salaries 511200 100
2010 Call Back Pay 511202
120
2010 Utilities 530004
400
2020 Overtime 511201 150
2030 Benefits 52000
200
2040 Periodicals 53000 250
3010 Salaries 511200
300
3010 Benefits 52000
350
4010 Salaries 511200
400

My goal is to create a report that would cherry pick a report that
would give me a summary of all 511XXX salary accounts totaled by
program. In this example I would expect to see Program 2010 to total
to $220. There are approximately 30 accounts that begin with the 511
prefix, so I think I need to use a wildcard to sum.

 

[Myrna Larson]

Assuming the account numbers are stored as numbers with 6 digits:

=SUMIF(C1:C10,">"&511000,D110)

If they're text with 6 characters

=SUMIF(C1:C10,"511???",D110)