Hi, everyone...
Building formulas is not yet a strong area of mine.
I'll keep it concise.

The scenario:

Given:
Columns A, B and D, respectively:
Vendor, Date, Amount.

I want to build a table of annual sums as follows:
Columns: G, H-Q, respectively:
Vendor, 1998-2006

Each vendor, then, will be listed once in the Vendor column (G). I
should be able to scan across columns H-Q for each vendor row and see
how much I spent at any given vendor in any given year.

Any working solutions would be greatly appreciated.

Thanks,

KZ

In H1:Q1, input the year numbers

In G2:Gn, add all vendor ids

In H2, add

=SUMPRODUCT(--($A$2:$A$20=$G2),--(YEAR($B$2:$B$20)=H$1),$C$2:$C$20)

and copy down and across

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ken Zenachon" wrote in message
ups.com...
Hi, everyone...
Building formulas is not yet a strong area of mine.
I'll keep it concise.

The scenario:

Given:
Columns A, B and D, respectively:
Vendor, Date, Amount.

I want to build a table of annual sums as follows:
Columns: G, H-Q, respectively:
Vendor, 1998-2006

Each vendor, then, will be listed once in the Vendor column (G). I
should be able to scan across columns H-Q for each vendor row and see
how much I spent at any given vendor in any given year.

Any working solutions would be greatly appreciated.

Thanks,

KZ

The easiest way would be to use a pivot table. Use the vendor as your
row field, the date as the column field and the Amount as the data
field. Then right click on one of the date headings and choose "Group
and Show Detail" and then "Group". Click on years and make sure no other
selections are made and then Ok.

Otherwise if you wanted to do it with formulas enter the vendor names in
G2 downwards and in H1 - Q1 enter 1998, 1999, 2000 etc.
In H2 enter the formula:
=SUMPRODUCT(($A$2:$A$100=$G2)*(YEAR($B$2:$B$100)=H $1)*$C$2:$C$100)
and adjust the ranges to suit your data (I've used rows 2 to 100).
Then copy down and across as required.

Hope this helps
Rowan

Ken Zenachon wrote:
Hi, everyone...
Building formulas is not yet a strong area of mine.
I'll keep it concise.

The scenario:

Given:
Columns A, B and D, respectively:
Vendor, Date, Amount.

I want to build a table of annual sums as follows:
Columns: G, H-Q, respectively:
Vendor, 1998-2006

Each vendor, then, will be listed once in the Vendor column (G). I
should be able to scan across columns H-Q for each vendor row and see
how much I spent at any given vendor in any given year.

Any working solutions would be greatly appreciated.

Thanks,

KZ

Hey, guys, thanks for the replies.
I'm not able to implement them successfully as of yet, but given my
level of frustration over this project tonight and my unfamiliarity
with the operations you outlined it's no surprise. I'm gonna leave it
till tomorrow.

Will keep you posted.

Thanks again,


]-[

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.

]-[

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?

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards,

Peo Sjoblom

(No private emails please)


"Ken Zenachon" wrote in message
oups.com...
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.

]-[

Bob, who suggested the SumProduct solution, is the author of a web site,
where an entire section is devoted to explaining the intricacies of the
SumProduct() function.

This function has evolved way beyond what the originators at Redmond had
originally designed it for, so that's why there's really nothing definitive
in the XL Help files.
That evolution is primarily due to the efforts and expertise of the folks
who frequent these groups.

Grab a pot of coffee, OR a bottle of adult beverage, and read a little:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ken Zenachon" wrote in message
oups.com...
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.

]-[

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?

]-[

Hey, folks,

Just wanted to let you all know that with your help (no small part of
which constitues Bob's tutorial) I taught myself enough about the funky
uses of SUMPRODUCT to create a working formula all by my lonesome.

Teach a man to fish...


KZ

Hi Ken

Whilst I see from other postings in this thread you have reached a solution
with Sumproduct which will do exactly what you want, I have to contest your
assertions regarding Pivot Tables.

They will do just as you want.
As Rowan said in his posting, you can Group dates by Years.
Pivot tables will automatically sort themselves, in whatever way you define.
A very simple one line macro pasted onto the sheet holding the Pivot table
will cause it to automatically refresh, each time you view the sheet.

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
End Sub

Copy the above macro, right click on the sheet tab with the Pivot table and
paste the code into the white pane at the right of the screen. You may need
to change "PivotTable1" if your Pivot has a different name (this can be
determined by looking at Table Options from the PT toolbar)

For more information on Pivot Table use, take a look at Debra Dalgleish's site
http://www.contextures.com/xlPivot07.html

Do persevere with Pivot Tables, they were destined for exactly the sort of
data analysis you are trying to do, and once learned will allow you to do
things without having to type any formulae.

Regards

Roger Govier


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.

]-[

Hi, Roger,

The reason I thought PivotTables wouldn't work was because I kept
getting a "Cannot Group that Selection" error when I tried invoking the
Group command. The site you linked to, however, sorted that out for me
(as did a quick Google search on the matter).

Moving on, right-clicking on the sheet tab brings up a context menu and
no white are at the right of the screen. Should I click on "View Code"
in this context menu and past the macro into the VBA sheet that
appears?

Also, I apply formats to my PivotTable but every time I refresh the
data the formats are lost and the table is resized. How can I make
formats stick?

KZ

Also, is there any way to get the PivotTable to refresh as soon as I
enter new data?


]-[

Hi, Roger,

The reason I thought PivotTables wouldn't work was because I kept
getting a "Cannot Group that Selection" error when I tried invoking the
Group command. The site you linked to, however, sorted that out for me
(as did a quick Google search on the matter).

Moving on, right-clicking on the sheet tab as you suggested just brings
up a context menu and no white area at the right of the screen. Should
I click on "View Code" in this context menu and past the macro into the
VBA sheet that appears?

Also, I apply formats to my PivotTable but every time I refresh the
data the formats are lost and the table is resized. How can I make
formats stick?

Also also, is there any way to get the PivotTable to refresh as soon as
I
enter new data?

]-[

Hi Ken

Sorry I missed the Select View Code option in my original posting, before
saying paste the code provided.

After applying your formats, from the PT toolbar, select the dropdown for
Pivot Table and select Table Options. Remove the checkmark from Autoformat
table.

If your data is on a separate page to the PT, then when you select the tab
with the PT the data will automatically refresh.
If you have the PT on the same sheet as your data, then you would need to
create a Selection Change macro for the column where the data is entered, to
fire the same macro as provided.


Regards

Roger Govier


Ken Zenachon wrote:
Hi, Roger,

The reason I thought PivotTables wouldn't work was because I kept
getting a "Cannot Group that Selection" error when I tried invoking the
Group command. The site you linked to, however, sorted that out for me
(as did a quick Google search on the matter).

Moving on, right-clicking on the sheet tab as you suggested just brings
up a context menu and no white area at the right of the screen. Should
I click on "View Code" in this context menu and past the macro into the
VBA sheet that appears?

Also, I apply formats to my PivotTable but every time I refresh the
data the formats are lost and the table is resized. How can I make
formats stick?

Also also, is there any way to get the PivotTable to refresh as soon as
I
enter new data?

]-[

And if you want your pivot table to automatically include new rows of
data added you can base the pivot table input on a dynamic named range.

See http://contextures.com/xlNames01.html#Dynamic for dynamic named ranges.

Hope this helps
Rowan

Roger Govier wrote:
Hi Ken

Sorry I missed the Select View Code option in my original posting,
before saying paste the code provided.

After applying your formats, from the PT toolbar, select the dropdown
for Pivot Table and select Table Options. Remove the checkmark from
Autoformat table.

If your data is on a separate page to the PT, then when you select the
tab with the PT the data will automatically refresh.
If you have the PT on the same sheet as your data, then you would need
to create a Selection Change macro for the column where the data is
entered, to fire the same macro as provided.


Regards

Roger Govier


Ken Zenachon wrote:

Hi, Roger,

The reason I thought PivotTables wouldn't work was because I kept
getting a "Cannot Group that Selection" error when I tried invoking the
Group command. The site you linked to, however, sorted that out for me
(as did a quick Google search on the matter).

Moving on, right-clicking on the sheet tab as you suggested just brings
up a context menu and no white area at the right of the screen. Should
I click on "View Code" in this context menu and past the macro into the
VBA sheet that appears?

Also, I apply formats to my PivotTable but every time I refresh the
data the formats are lost and the table is resized. How can I make
formats stick?

Also also, is there any way to get the PivotTable to refresh as soon as
I
enter new data?

]-[

Good point Rowan, I had missed that in my suggestions.

Regards

Roger Govier


Rowan Drummond wrote:
And if you want your pivot table to automatically include new rows of
data added you can base the pivot table input on a dynamic named range.

See http://contextures.com/xlNames01.html#Dynamic for dynamic named ranges.

Hope this helps
Rowan

Roger Govier wrote:

Hi Ken

Sorry I missed the Select View Code option in my original posting,
before saying paste the code provided.

After applying your formats, from the PT toolbar, select the dropdown
for Pivot Table and select Table Options. Remove the checkmark from
Autoformat table.

If your data is on a separate page to the PT, then when you select the
tab with the PT the data will automatically refresh.
If you have the PT on the same sheet as your data, then you would need
to create a Selection Change macro for the column where the data is
entered, to fire the same macro as provided.


Regards

Roger Govier


Ken Zenachon wrote:

Hi, Roger,

The reason I thought PivotTables wouldn't work was because I kept
getting a "Cannot Group that Selection" error when I tried invoking the
Group command. The site you linked to, however, sorted that out for me
(as did a quick Google search on the matter).

Moving on, right-clicking on the sheet tab as you suggested just brings
up a context menu and no white area at the right of the screen. Should
I click on "View Code" in this context menu and past the macro into the
VBA sheet that appears?

Also, I apply formats to my PivotTable but every time I refresh the
data the formats are lost and the table is resized. How can I make
formats stick?

Also also, is there any way to get the PivotTable to refresh as soon as
I
enter new data?

]-[

Thanks, everyone.
Everything's working like a charm.

KZ