Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
a921
 
Posts: n/a
Default =SUMIF(B2:B13,"=Jan",C2:C13)

I'm looking to use the "sumif" formula listed above.
However my data lists Jan-03 and Jan-04. How do I account
for the years in the above formula? Thanks in advance!!
  #2   Report Post  
Myrna Larson
 
Posts: n/a
Default

If you want to sum January for both years and the data is actual text, not the
date Jan 1, 2003, formatted to show Jan-03,

=SUMIF(B2:B13,"Jan*",C2:C13)

note the wildcard.


On Wed, 26 Jan 2005 13:48:14 -0800, "a921"
wrote:

I'm looking to use the "sumif" formula listed above.
However my data lists Jan-03 and Jan-04. How do I account
for the years in the above formula? Thanks in advance!!


  #3   Report Post  
Rob van Gelder
 
Posts: n/a
Default

=SUMPRODUCT(--(MONTH(B2:B13)=1), C2:C13)

For a good article on advanced uses of SUMPRODUCT:
www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"a921" wrote in message
...
I'm looking to use the "sumif" formula listed above.
However my data lists Jan-03 and Jan-04. How do I account
for the years in the above formula? Thanks in advance!!



  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

If you only would want to pick up Jan-values of a certain year (say,
2004) and B2:B13 houses true dates...

In D2 enter: 1-Jan-2004

In E2 enter:

=DATE(YEAR(D2),MONTH(D2)+1,0)

then invoke:

=SUMIF(B2:B13,"="&D2,C2:C13)-SUMIF(B2:B13,""&E2,C2:C13)

a921 wrote:
I'm looking to use the "sumif" formula listed above.
However my data lists Jan-03 and Jan-04. How do I account
for the years in the above formula? Thanks in advance!!

  #5   Report Post  
Naz
 
Posts: n/a
Default

Unfortunately there is no way of changing the Autofilter colour.

Hope that helps

__________________
Naz
London

"Aladin Akyurek" wrote:

If you only would want to pick up Jan-values of a certain year (say,
2004) and B2:B13 houses true dates...

In D2 enter: 1-Jan-2004

In E2 enter:

=DATE(YEAR(D2),MONTH(D2)+1,0)

then invoke:

=SUMIF(B2:B13,"="&D2,C2:C13)-SUMIF(B2:B13,""&E2,C2:C13)

a921 wrote:
I'm looking to use the "sumif" formula listed above.
However my data lists Jan-03 and Jan-04. How do I account
for the years in the above formula? Thanks in advance!!




  #6   Report Post  
Naz
 
Posts: n/a
Default

whoops wrong post

"Naz" wrote:

Unfortunately there is no way of changing the Autofilter colour.

Hope that helps

__________________
Naz
London

"Aladin Akyurek" wrote:

If you only would want to pick up Jan-values of a certain year (say,
2004) and B2:B13 houses true dates...

In D2 enter: 1-Jan-2004

In E2 enter:

=DATE(YEAR(D2),MONTH(D2)+1,0)

then invoke:

=SUMIF(B2:B13,"="&D2,C2:C13)-SUMIF(B2:B13,""&E2,C2:C13)

a921 wrote:
I'm looking to use the "sumif" formula listed above.
However my data lists Jan-03 and Jan-04. How do I account
for the years in the above formula? Thanks in advance!!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 04:58 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"