Array Formula or DSum or Pivot Table or Other suggestion?

[C. Bailey]

I track my finances in a spreadsheet. The dates are in a column, as well as
a description, amounts, etc. I want to develop a formula that will
summarize monthly expenses for a certain type of expenditure (e.g. all
expenditures in January, 2002 on utilities). I can do this with an array
formula similar to this:

=SUM(IF(AND(YEAR(Bank!$C$5:$C$10000)=Sheet1!D2,MONTH(Bank!$C$5:$C$10000)=She
et1!E2,Bank!$W$5:$W$10000=Sheet1!$F$1),Bank!$E$5:$E$10000+Bank!$G$5:$G$10000
+Bank!$I$5:$I$10000))

This formula slows Excel to a crawl. I haven't provided enough detail for
you to be able to optimize it because I really don't think this is the
solution. SUMIF won't work, because you can only check one condition.

I have tried a Pivot Table, but it hates grouping dates if you have any
blank cells. This means I have to recreate the pivot table each time I add
additional data to my sheet.

DSum seems to hold the most promise, but the drawback is that I can't copy
the formula down the page, because you need the criteria labels immediately
above the criteria (in this case the date labels). Thus, I can only group
one month at a time.

Any other suggestions? Anyone have examples on a webpage?

Chris

 

[acw]

Chris

Have a look at the sumproduct function.


Tony

 

[steve]

I run tally's on many divisions by month.
Column A contains entry name (sales, costs, etc). Columns B to M contain
the amounts for each. Column N is a YTD Total for each.
This structure repeats down the sheet for each division.

Below everything is a summary for all. It also has the entry names in
column A. columns B to N have the formula (in R1C1 notation)
=SUMIF(R1C1:R[-1]C1,RC1,R1C:R[-1]C)
This creates a sum of all the divisions for that particular item. I than
fill right and down for all the others.

In your case column A could be the equivalent of "utilities", "groceries",
etc.
Change C1 to what ever column contains these descriptions.