# countif and sumif

pbuscio@comcast.net
Guest
Posts: n/a

 10th Aug 2012
I am trying to count the cells in a sheet that have a certain year and a certain month. I am not sure how to write the criteria. Any help would be appreciated.

Thanks

Claus Busch
Guest
Posts: n/a

 10th Aug 2012
Hi,

Am Fri, 10 Aug 2012 08:32:25 -0700 (PDT) schrieb http://www.pcreview.co.uk/forums/(E-Mail Removed):

> I am trying to count the cells in a sheet that have a certain year and a certain month. I am not sure how to write the criteria. Any help would be appreciated.

your values in A1:A100, year = 2010, month = may:
=SUMPRODUCT(--(YEAR(A1:A100)=2010),--(MONTH(A1:A100)=5))

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

pbuscio@comcast.net
Guest
Posts: n/a

 10th Aug 2012
On Friday, August 10, 2012 11:37:40 AM UTC-4, Claus Busch wrote:
> Hi, Am Fri, 10 Aug 2012 08:32:25 -0700 (PDT) schrieb > I am trying to count the cells in a sheet that have a certain year and a certain month. I am not sure how to write the criteria. Any help would be appreciated. your values in A1:A100, year = 2010, month = may: =SUMPRODUCT(--(YEAR(A1:A100)=2010),--(MONTH(A1:A100)=5)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

Thanks. That works for counting. How about if I need to add the value from another range based on the counted cells. Like sumif?

Claus Busch
Guest
Posts: n/a

 10th Aug 2012
Hi,

Am Fri, 10 Aug 2012 08:56:12 -0700 (PDT) schrieb (E-Mail Removed):

> Thanks. That works for counting. How about if I need to add the value from another range based on the counted cells. Like sumif?

the values to sum in B1:B100:
=SUMPRODUCT(--(YEAR(A1:A100)=2010),--(MONTH(A1:A100)=5),B1:B100)

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post mwdawg721 Microsoft Excel Worksheet Functions 3 26th Oct 2009 10:41 PM =?Utf-8?B?SGFycnkgU2V5bW91cg==?= Microsoft Excel Worksheet Functions 9 12th Jun 2007 10:47 PM chinita_jill Microsoft Excel Misc 5 19th Jul 2006 05:22 PM =?Utf-8?B?a2F0aGk=?= Microsoft Excel Worksheet Functions 15 22nd Feb 2006 03:14 PM nshah Microsoft Excel Misc 3 27th May 2004 09:16 PM

Features