I have a worksheet with date a bottle of wine is drunk (formatted as
yyyy-mm-dd) and another column indicating whether the wine was Red or White
(White exists as White, or White - Sweet Dessert, so the string "Whit*"
captures all White).

I want a count of total reds, whites, and overall total bottles consumed by
year but do not see how to get this yet. For graphing purposes I would like
these three totals to appear as rows under column headings corresponding to
each year and beginning 2007 and going out to 2040. The base worksheet is
automatically refreshed from an internet based source, so it would be nice to
have a formula that works today and throughout the future.

In case the above is not clear, imagine I have:

Consumed Type
2007-06-23 White
2007-09-17 Red
2008-01-17 Red
2008-11-18 Red

I would want this to give

2007 2008 2009
Total Red 1 2 0 (or empty cell, etc)
Total White 1 0
Total 2 2


Thanks,

Dave M

Assume source data as posted in Sheet1's cols A and B, data from row2 down,
with real dates in A2 down

Then in your Totals sheet, assuming you have the years listed in B1 across
(2007, 2008, etc), with the colours (eg: Red, White) listed in A2 down (ie
w/o the text "Total")

In B2:
=SUMPRODUCT((Sheet1!$B$2:$B$100=$A2)*(YEAR(Sheet1! $A$2:$A$100)=B$1))
Copy across/fill down to populate. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Dave_in_gva" wrote:
I have a worksheet with date a bottle of wine is drunk (formatted as
yyyy-mm-dd) and another column indicating whether the wine was Red or White
(White exists as White, or White - Sweet Dessert, so the string "Whit*"
captures all White).

I want a count of total reds, whites, and overall total bottles consumed by
year but do not see how to get this yet. For graphing purposes I would like
these three totals to appear as rows under column headings corresponding to
each year and beginning 2007 and going out to 2040. The base worksheet is
automatically refreshed from an internet based source, so it would be nice to
have a formula that works today and throughout the future.

In case the above is not clear, imagine I have:

Consumed Type
2007-06-23 White
2007-09-17 Red
2008-01-17 Red
2008-11-18 Red

I would want this to give

2007 2008 2009
Total Red 1 2 0 (or empty cell, etc)
Total White 1 0
Total 2 2

Thanks Max, extremely helpful and works perfectly.

One other nuance, if I may. I would like to graph the total data of wines
consumed by year, but have these histograms show up as negative numbers,
giving me histograms passing below the x axis. Is there a simple way of
converting the totals your formula is giving me to negative numbers?

Thanks again,

Dave M

"Max" wrote:

Assume source data as posted in Sheet1's cols A and B, data from row2 down,
with real dates in A2 down

Then in your Totals sheet, assuming you have the years listed in B1 across
(2007, 2008, etc), with the colours (eg: Red, White) listed in A2 down (ie
w/o the text "Total")

In B2:
=SUMPRODUCT((Sheet1!$B$2:$B$100=$A2)*(YEAR(Sheet1! $A$2:$A$100)=B$1))
Copy across/fill down to populate. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Dave_in_gva" wrote:
I have a worksheet with date a bottle of wine is drunk (formatted as
yyyy-mm-dd) and another column indicating whether the wine was Red or White
(White exists as White, or White - Sweet Dessert, so the string "Whit*"
captures all White).

I want a count of total reds, whites, and overall total bottles consumed by
year but do not see how to get this yet. For graphing purposes I would like
these three totals to appear as rows under column headings corresponding to
each year and beginning 2007 and going out to 2040. The base worksheet is
automatically refreshed from an internet based source, so it would be nice to
have a formula that works today and throughout the future.

In case the above is not clear, imagine I have:

Consumed Type
2007-06-23 White
2007-09-17 Red
2008-01-17 Red
2008-11-18 Red

I would want this to give

2007 2008 2009
Total Red 1 2 0 (or empty cell, etc)
Total White 1 0
Total 2 2

Sorry Max....forget my stupid question. Just figured it out myself and did so
by adding =0-(your formula).

Duh....

"Dave_in_gva" wrote:

Thanks Max, extremely helpful and works perfectly.

One other nuance, if I may. I would like to graph the total data of wines
consumed by year, but have these histograms show up as negative numbers,
giving me histograms passing below the x axis. Is there a simple way of
converting the totals your formula is giving me to negative numbers?

Thanks again,

Dave M

"Max" wrote:

Assume source data as posted in Sheet1's cols A and B, data from row2 down,
with real dates in A2 down

Then in your Totals sheet, assuming you have the years listed in B1 across
(2007, 2008, etc), with the colours (eg: Red, White) listed in A2 down (ie
w/o the text "Total")

In B2:
=SUMPRODUCT((Sheet1!$B$2:$B$100=$A2)*(YEAR(Sheet1! $A$2:$A$100)=B$1))
Copy across/fill down to populate. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Dave_in_gva" wrote:
I have a worksheet with date a bottle of wine is drunk (formatted as
yyyy-mm-dd) and another column indicating whether the wine was Red or White
(White exists as White, or White - Sweet Dessert, so the string "Whit*"
captures all White).

I want a count of total reds, whites, and overall total bottles consumed by
year but do not see how to get this yet. For graphing purposes I would like
these three totals to appear as rows under column headings corresponding to
each year and beginning 2007 and going out to 2040. The base worksheet is
automatically refreshed from an internet based source, so it would be nice to
have a formula that works today and throughout the future.

In case the above is not clear, imagine I have:

Consumed Type
2007-06-23 White
2007-09-17 Red
2008-01-17 Red
2008-11-18 Red

I would want this to give

2007 2008 2009
Total Red 1 2 0 (or empty cell, etc)
Total White 1 0
Total 2 2

No prob. Glad you figured it out.
You can omit the zero in the tweak
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Dave_in_gva" wrote in message
...
Sorry Max....forget my stupid question. Just figured it out myself and did
so
by adding =0-(your formula).

Check. Tx again.

In case you have time/interest/ability I have posted another query in this
forum with the title New worksheets in webquery.xls not automatically
refreshing....awaiting help with this eagerly if possible.

Thanks again for your help with this query.

Dave

"Max" wrote:

No prob. Glad you figured it out.
You can omit the zero in the tweak
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Dave_in_gva" wrote in message
...
Sorry Max....forget my stupid question. Just figured it out myself and did
so
by adding =0-(your formula).

In case you have time/interest/ability I have posted another query in this
forum with the title New worksheets in webquery.xls not automatically
refreshing....awaiting help with this eagerly if possible.

Yes, I read it. But I'm not sure if it has something to do with the degree
of calc intensity involved. Sumproduct, especially with large ranges, is
v.calc intensive. Charts are also calc intensive, and could be a
contributing factor. Does it update ok if you try minimizing the sumproduct
ranges and/or if you dissociate/move the charts into separate book (plot
from frozen data). Hang in there awhile for better answers from others in
your new posting. Good luck.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---