Totals by Year

M

Mike Copeland

I have a worksheet (of wines I've accumulated over several years) and
I'd like to produce sum totals of prices/costs for each year. I presume
I want to use the sumif function, but I don't know how to use the date
values in the criteria. That is, ">= 1/1/2007 & <= 12/31/2007" doesn't
look like a valid selection value range. How do I use a date field in
this way (or is there a better way?)? TIA

Cabernet Sauvignon Montes` 7.89 09/15/2007
Cabernet Sauvignon Riven Rock 5.99 04/14/2007
Cabernet Sauvignon Robert Mondavi 0.00 12/22/2006
Cabernet Sauvignon Spiral 4.99 12/21/2010
Cabernet Sauvignon Trader Joe's 4.99 12/21/2010
Cabernet Sauvignon Veo Grande 6.98 12/16/2007
Champagne Mumm Napa 13.99 11/27/2007
Champagne Mumm Napa 0.00 12/22/2006
Champagne Tott's 5.97 05/24/2008
 
I

isabelle

hi Mike,

with the start date in cell E1
and the end date in cell F1

=SUMPRODUCT(--(dt>=E1)*(dt<=F1)*(price))

do i was be on the guest list ?

--
isabelle




Le 2012-01-25 00:17, Mike Copeland a écrit :
 
M

Mike Copeland

hi Mike,

with the start date in cell E1
and the end date in cell F1

=SUMPRODUCT(--(dt>=E1)*(dt<=F1)*(price))

do i was be on the guest list ?
Click to expand...
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Thanks for the help.
Sure, you're invited, but note that my wines are pretty cheap and you
might want better stuff... 8<}}
 
D

Don Guillett

   I have a worksheet (of wines I've accumulated over several years) and
I'd like to produce sum totals of prices/costs for each year.  I presume
I want to use the sumif function, but I don't know how to use the date
values in the criteria.  That is, ">= 1/1/2007 & <= 12/31/2007" doesn't
look like a valid selection value range.  How do I use a date field in
this way (or is there a better way?)?  TIA

Cabernet Sauvignon Montes`            7.89  09/15/2007
Cabernet Sauvignon Riven Rock     5.99  04/14/2007
Cabernet Sauvignon Robert Mondavi 0.00  12/22/2006
Cabernet Sauvignon Spiral         4.99  12/21/2010
Cabernet Sauvignon Trader Joe's   4.99  12/21/2010
Cabernet Sauvignon Veo Grande     6.98  12/16/2007
Champagne          Mumm Napa     13.99  11/27/2007
Champagne          Mumm Napa    0.00  12/22/2006
Champagne          Tott's              5.97  05/24/2008
Click to expand...

sumproduct((year(d2:d22)=2007)*c2:c22)
or
sumproduct((year(d2:d22)=2007)*(a2:a22="Champagne")*c2:c22)
etc
 
Top