+ Reply to Thread
Results 1 to 8 of 8

SumProduct or CountIf

  1. #1
    Kim
    Guest

    SumProduct or CountIf

    I am trying to implement the following function to count the number of
    entries I have for January, February, etc.

    =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January"))

    It works fine for February, March, etc. but not for January because it reads
    the empty cells as being 01/01/1901. Any suggestions? Thanks.

  2. #2
    Roger Govier
    Guest

    Re: SumProduct or CountIf


    Hi Kim

    Try
    =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetName!J5:J8,<>))

    --
    Regards
    Roger Govier
    "Kim" <Kim@discussions.microsoft.com> wrote in message
    news:A195C9BE-69E6-4300-B1EB-97B08F34E5AE@microsoft.com...
    >I am trying to implement the following function to count the number of
    > entries I have for January, February, etc.
    >
    > =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January"))
    >
    > It works fine for February, March, etc. but not for January because it
    > reads
    > the empty cells as being 01/01/1901. Any suggestions? Thanks.




  3. #3
    Kim
    Guest

    Re: SumProduct or CountIf

    Thank you. I could not get this to work. Excel tells me my function
    contains an error.

    "Roger Govier" wrote:

    >
    > Hi Kim
    >
    > Try
    > =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetName!J5:J8,<>))
    >
    > --
    > Regards
    > Roger Govier
    > "Kim" <Kim@discussions.microsoft.com> wrote in message
    > news:A195C9BE-69E6-4300-B1EB-97B08F34E5AE@microsoft.com...
    > >I am trying to implement the following function to count the number of
    > > entries I have for January, February, etc.
    > >
    > > =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January"))
    > >
    > > It works fine for February, March, etc. but not for January because it
    > > reads
    > > the empty cells as being 01/01/1901. Any suggestions? Thanks.

    >
    >
    >


  4. #4
    JE McGimpsey
    Guest

    Re: SumProduct or CountIf

    That gives me an error. Perhaps something like this, instead:

    =SUMPRODUCT(--(MONTH(SheetName!J5:J8)=1),--(SheetName!J5:J8<>""))


    Note that if you're going to do the array multiplication first, using *,
    there's no reason to use the double unary minuses (--). If you're going
    to let SUMPRODUCT to the array multiplication by using the comma
    notation, the double unary minuses are needed. The latter is slightly
    more efficient.

    In article <ePRMg#$gFHA.3656@TK2MSFTNGP09.phx.gbl>,
    "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote:

    > Hi Kim
    >
    > Try
    > =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetName!J5:J8,<>))


  5. #5
    JE McGimpsey
    Guest

    Re: SumProduct or CountIf

    Correction:

    With multiple terms, one only need use at least one unary minus per
    conditional, as long as the total number of unary minuses is even.

    I find it's easier just to use double unary minuses for each conditional
    all the time, if only to avoid thinking/explaining.


    In article <jemcgimpsey-C2F919.15251408072005@msnews.microsoft.com>,
    JE McGimpsey <jemcgimpsey@mvps.org> wrote:

    > If you're going to let SUMPRODUCT to the array multiplication by
    > using the comma notation, the double unary minuses are needed. The
    > latter is slightly more efficient.


  6. #6
    Kim
    Guest

    Re: SumProduct or CountIf

    Thank you. I got it to work.
    =SUMPRODUCT(--(TEXT(SheetName!K4:K800, "mmmm")="January"),
    --(Countrywide!K4:K800<>0))


    "JE McGimpsey" wrote:

    > That gives me an error. Perhaps something like this, instead:
    >
    > =SUMPRODUCT(--(MONTH(SheetName!J5:J8)=1),--(SheetName!J5:J8<>""))
    >
    >
    > Note that if you're going to do the array multiplication first, using *,
    > there's no reason to use the double unary minuses (--). If you're going
    > to let SUMPRODUCT to the array multiplication by using the comma
    > notation, the double unary minuses are needed. The latter is slightly
    > more efficient.
    >
    > In article <ePRMg#$gFHA.3656@TK2MSFTNGP09.phx.gbl>,
    > "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote:
    >
    > > Hi Kim
    > >
    > > Try
    > > =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetName!J5:J8,<>))

    >


  7. #7
    Dave Peterson
    Guest

    Re: SumProduct or CountIf

    You may want to add that --(SheetName!K4:K800<>"") portion into your formula.

    Try clearing the contents on sheetname!K4.

    An empty cell will look like January when you do: =text(a1,"mmmm")

    Kim wrote:
    >
    > Thank you. I got it to work.
    > =SUMPRODUCT(--(TEXT(SheetName!K4:K800, "mmmm")="January"),
    > --(Countrywide!K4:K800<>0))
    >
    > "JE McGimpsey" wrote:
    >
    > > That gives me an error. Perhaps something like this, instead:
    > >
    > > =SUMPRODUCT(--(MONTH(SheetName!J5:J8)=1),--(SheetName!J5:J8<>""))
    > >
    > >
    > > Note that if you're going to do the array multiplication first, using *,
    > > there's no reason to use the double unary minuses (--). If you're going
    > > to let SUMPRODUCT to the array multiplication by using the comma
    > > notation, the double unary minuses are needed. The latter is slightly
    > > more efficient.
    > >
    > > In article <ePRMg#$gFHA.3656@TK2MSFTNGP09.phx.gbl>,
    > > "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote:
    > >
    > > > Hi Kim
    > > >
    > > > Try
    > > > =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetName!J5:J8,<>))

    > >


    --

    Dave Peterson

  8. #8
    KL
    Guest

    Re: SumProduct or CountIf

    JFYI: this formula won't work in any other version of the Office, but
    English. If your application may be used internationally you're better off
    using the formula proposed by JE McGimpsey

    Regards,
    KL

    "Kim" <Kim@discussions.microsoft.com> wrote in message
    news:D6593231-3BBB-4F5A-A600-EDF75ED048DA@microsoft.com...
    > Thank you. I got it to work.
    > =SUMPRODUCT(--(TEXT(SheetName!K4:K800, "mmmm")="January"),
    > --(Countrywide!K4:K800<>0))
    >
    >
    > "JE McGimpsey" wrote:
    >
    >> That gives me an error. Perhaps something like this, instead:
    >>
    >> =SUMPRODUCT(--(MONTH(SheetName!J5:J8)=1),--(SheetName!J5:J8<>""))
    >>
    >>
    >> Note that if you're going to do the array multiplication first, using *,
    >> there's no reason to use the double unary minuses (--). If you're going
    >> to let SUMPRODUCT to the array multiplication by using the comma
    >> notation, the double unary minuses are needed. The latter is slightly
    >> more efficient.
    >>
    >> In article <ePRMg#$gFHA.3656@TK2MSFTNGP09.phx.gbl>,
    >> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote:
    >>
    >> > Hi Kim
    >> >
    >> > Try
    >> > =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetName!J5:J8,<>))

    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1