countif and sumif

Discussion in 'Microsoft Excel Worksheet Functions' started by pbuscio@comcast.net, Aug 10, 2012.

  1. Guest

    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
     
    , Aug 10, 2012
    #1
    1. Advertisements

  2. Claus Busch Guest

    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
     
    Claus Busch, Aug 10, 2012
    #2
    1. Advertisements

  3. Guest

    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?
     
    , Aug 10, 2012
    #3
  4. Claus Busch Guest

    Hi,

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

    > 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
     
    Claus Busch, Aug 10, 2012
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. deacs

    COUNTIF and SUMIF Functions when there are 2 criteria

    deacs, Nov 6, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    207
    deacs
    Nov 6, 2003
  2. Etien

    WorksheetFunction.CountIf and WorksheetFunction.SumIf with 2 conditions?

    Etien, Jan 12, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    741
    Harlan Grove
    Jan 12, 2004
  3. Guest

    AVERAGEIF AND SUMIF AND COUNTIF

    Guest, Feb 21, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    15
    Views:
    283
    Don Guillett
    Feb 22, 2006
  4. Guest

    sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A"

    Guest, Jun 12, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    9
    Views:
    613
    Bob Phillips
    Jun 12, 2007
  5. mwdawg721

    Copying and pasting with SUMIF and COUNTIF

    mwdawg721, Oct 26, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    271
    Gord Dibben
    Oct 26, 2009
Loading...

Share This Page