OK, I calmed down and read up on PivotTables.

I don't think a PivotTable is the best solution for me in this case,
for several reasons. For starters, for the Invoice Date data field
Excel creates a separate column for every transaction; hundreds of
columns, which I then have to manually group by year. This would be
fine if I needed to do it just once. However, additional transactions
don't automatically sort themselves into the appropriate year group.
Also, PivotTables don't update automatically, requiring me to manually
refresh the table if I want to see (and sort) the new data.

OK, as for SUMPRODUCT, I read up on it in Excel's help file and the
examples they give for it are nothing like what you are suggesting,
leaving me at a loss as to what's happening in your formulas. I mean, I
*kind of* see what's going on but I'm missing a solid understanding of
the workings of this function.

I figured I wanted to add certain numbers based on multiple criteria
that I'd have to create a statement in the form of SUM(IF()). Aparantly
not. Pease explain.

Also, Bob and Rowan, you each seem to have a completely different
understanding of how to use the SUMPRODUCT function. Could one or both
of you expound briefly on your methods?

Hi Ken

Bob has a brilliant article describing the use of Sumproduct at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

You will see from this that our two solutions are essentially the same
thing.

Hope this helps
Rowan

Ken Zenachon wrote:
OK, I calmed down and read up on PivotTables.

I don't think a PivotTable is the best solution for me in this case,
for several reasons. For starters, for the Invoice Date data field
Excel creates a separate column for every transaction; hundreds of
columns, which I then have to manually group by year. This would be
fine if I needed to do it just once. However, additional transactions
don't automatically sort themselves into the appropriate year group.
Also, PivotTables don't update automatically, requiring me to manually
refresh the table if I want to see (and sort) the new data.

OK, as for SUMPRODUCT, I read up on it in Excel's help file and the
examples they give for it are nothing like what you are suggesting,
leaving me at a loss as to what's happening in your formulas. I mean, I
*kind of* see what's going on but I'm missing a solid understanding of
the workings of this function.

I figured I wanted to add certain numbers based on multiple criteria
that I'd have to create a statement in the form of SUM(IF()). Aparantly
not. Pease explain.

Also, Bob and Rowan, you each seem to have a completely different
understanding of how to use the SUMPRODUCT function. Could one or both
of you expound briefly on your methods?

Wow, thanks everyone, that really clears things up!
Bob, I gather you get this a lot around here-- Great article!

The creative misuse of tools, man, I'm all over it!

Just as an aside, how might one construct a multiple condition test for
my problem using traditional (non-creative) methods?

]-[