+ Reply to Thread
Results 1 to 9 of 9

Wildcard with sumif

  1. #1
    fractallinda
    Guest

    Wildcard with sumif

    I'm trying to get the sum of a list of numbers that start with a certain
    number (in this example 1). I can't seem to make any wildcard work. My
    numbers all have 5 digits so I've tried 1* and 1????.
    =SUMIF(A1:A307,"1*",F1:F307)
    =SUMIF(A1:A307,"1????",F1:F307)
    don't work. Will someone please tell me what I'm doing incorrectly? Thanks
    in advance.

  2. #2
    Roger Govier
    Guest

    Re: Wildcard with sumif

    Hi

    One way
    =SUMPRODUCT(--(LEFT(A1:A307)="1"),F1:F307)

    --
    Regards

    Roger Govier


    "fractallinda" <[email protected]> wrote in message
    news:[email protected]...
    > I'm trying to get the sum of a list of numbers that start with a
    > certain
    > number (in this example 1). I can't seem to make any wildcard work.
    > My
    > numbers all have 5 digits so I've tried 1* and 1????.
    > =SUMIF(A1:A307,"1*",F1:F307)
    > =SUMIF(A1:A307,"1????",F1:F307)
    > don't work. Will someone please tell me what I'm doing incorrectly?
    > Thanks
    > in advance.




  3. #3
    fractallinda
    Guest

    RE: Wildcard with sumif

    terrific!! It worked great!
    What is the "--" ? I had tried that formula without -- and it didn't work.
    Thank you SO much

    "fractallinda" wrote:

    > I'm trying to get the sum of a list of numbers that start with a certain
    > number (in this example 1). I can't seem to make any wildcard work. My
    > numbers all have 5 digits so I've tried 1* and 1????.
    > =SUMIF(A1:A307,"1*",F1:F307)
    > =SUMIF(A1:A307,"1????",F1:F307)
    > don't work. Will someone please tell me what I'm doing incorrectly? Thanks
    > in advance.


  4. #4
    Roger Govier
    Guest

    Re: Wildcard with sumif

    Hi

    The comparison on column A will return True or False. In order to
    convert theses values to 1 and 0, the double unary minus (--) is used to
    coerce them, so the results will either be
    1 * F Value or
    0 * F Value
    which then gets summed by Sumproduct.

    --
    Regards

    Roger Govier


    "fractallinda" <[email protected]> wrote in message
    news:[email protected]...
    > terrific!! It worked great!
    > What is the "--" ? I had tried that formula without -- and it didn't
    > work.
    > Thank you SO much
    >
    > "fractallinda" wrote:
    >
    >> I'm trying to get the sum of a list of numbers that start with a
    >> certain
    >> number (in this example 1). I can't seem to make any wildcard work.
    >> My
    >> numbers all have 5 digits so I've tried 1* and 1????.
    >> =SUMIF(A1:A307,"1*",F1:F307)
    >> =SUMIF(A1:A307,"1????",F1:F307)
    >> don't work. Will someone please tell me what I'm doing incorrectly?
    >> Thanks
    >> in advance.




  5. #5
    Chip Pearson
    Guest

    Re: Wildcard with sumif

    > What is the "--" ?

    The comparison (LEFT(A1:A307)="1") returns an array of TRUE and
    FALSE values. These need to be converted to their numeric
    equivalent (1 and 0) so that they can be summed by SUMPRODUCT.
    The double negation (--) takes the negative of the negative to
    convert the TRUE and FALSE values to 1 and 0.


    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com



    "fractallinda" <[email protected]> wrote in
    message
    news:[email protected]...
    > terrific!! It worked great!
    > What is the "--" ? I had tried that formula without -- and it
    > didn't work.
    > Thank you SO much
    >
    > "fractallinda" wrote:
    >
    >> I'm trying to get the sum of a list of numbers that start with
    >> a certain
    >> number (in this example 1). I can't seem to make any wildcard
    >> work. My
    >> numbers all have 5 digits so I've tried 1* and 1????.
    >> =SUMIF(A1:A307,"1*",F1:F307)
    >> =SUMIF(A1:A307,"1????",F1:F307)
    >> don't work. Will someone please tell me what I'm doing
    >> incorrectly? Thanks
    >> in advance.




  6. #6
    Sloth
    Guest

    RE: Wildcard with sumif

    the "--" is used to convert an array of logical values (ie {TRUE, TRUE,
    FALSE, TRUE}), to an array of 1's and 0's (ie {1,1,0,1}). This is necessary
    because SUMPRODUCT will skip anything other than numerical values (like the
    SUM function).

    "fractallinda" wrote:

    > terrific!! It worked great!
    > What is the "--" ? I had tried that formula without -- and it didn't work.
    > Thank you SO much
    >
    > "fractallinda" wrote:
    >
    > > I'm trying to get the sum of a list of numbers that start with a certain
    > > number (in this example 1). I can't seem to make any wildcard work. My
    > > numbers all have 5 digits so I've tried 1* and 1????.
    > > =SUMIF(A1:A307,"1*",F1:F307)
    > > =SUMIF(A1:A307,"1????",F1:F307)
    > > don't work. Will someone please tell me what I'm doing incorrectly? Thanks
    > > in advance.


  7. #7
    fractallinda
    Guest

    Re: Wildcard with sumif

    many thanks!

    "Roger Govier" wrote:

    > Hi
    >
    > One way
    > =SUMPRODUCT(--(LEFT(A1:A307)="1"),F1:F307)
    >
    > --
    > Regards
    >
    > Roger Govier
    >
    >
    > "fractallinda" <[email protected]> wrote in message
    > news:[email protected]...
    > > I'm trying to get the sum of a list of numbers that start with a
    > > certain
    > > number (in this example 1). I can't seem to make any wildcard work.
    > > My
    > > numbers all have 5 digits so I've tried 1* and 1????.
    > > =SUMIF(A1:A307,"1*",F1:F307)
    > > =SUMIF(A1:A307,"1????",F1:F307)
    > > don't work. Will someone please tell me what I'm doing incorrectly?
    > > Thanks
    > > in advance.

    >
    >
    >


  8. #8
    fractallinda
    Guest

    Re: Wildcard with sumif

    thank you very much

    "Chip Pearson" wrote:

    > > What is the "--" ?

    >
    > The comparison (LEFT(A1:A307)="1") returns an array of TRUE and
    > FALSE values. These need to be converted to their numeric
    > equivalent (1 and 0) so that they can be summed by SUMPRODUCT.
    > The double negation (--) takes the negative of the negative to
    > convert the TRUE and FALSE values to 1 and 0.
    >
    >
    > --
    > Cordially,
    > Chip Pearson
    > Microsoft MVP - Excel
    > Pearson Software Consulting, LLC
    > www.cpearson.com
    >
    >
    >
    > "fractallinda" <[email protected]> wrote in
    > message
    > news:[email protected]...
    > > terrific!! It worked great!
    > > What is the "--" ? I had tried that formula without -- and it
    > > didn't work.
    > > Thank you SO much
    > >
    > > "fractallinda" wrote:
    > >
    > >> I'm trying to get the sum of a list of numbers that start with
    > >> a certain
    > >> number (in this example 1). I can't seem to make any wildcard
    > >> work. My
    > >> numbers all have 5 digits so I've tried 1* and 1????.
    > >> =SUMIF(A1:A307,"1*",F1:F307)
    > >> =SUMIF(A1:A307,"1????",F1:F307)
    > >> don't work. Will someone please tell me what I'm doing
    > >> incorrectly? Thanks
    > >> in advance.

    >
    >
    >


  9. #9
    fractallinda
    Guest

    RE: Wildcard with sumif

    thank you!

    "Sloth" wrote:

    > the "--" is used to convert an array of logical values (ie {TRUE, TRUE,
    > FALSE, TRUE}), to an array of 1's and 0's (ie {1,1,0,1}). This is necessary
    > because SUMPRODUCT will skip anything other than numerical values (like the
    > SUM function).
    >
    > "fractallinda" wrote:
    >
    > > terrific!! It worked great!
    > > What is the "--" ? I had tried that formula without -- and it didn't work.
    > > Thank you SO much
    > >
    > > "fractallinda" wrote:
    > >
    > > > I'm trying to get the sum of a list of numbers that start with a certain
    > > > number (in this example 1). I can't seem to make any wildcard work. My
    > > > numbers all have 5 digits so I've tried 1* and 1????.
    > > > =SUMIF(A1:A307,"1*",F1:F307)
    > > > =SUMIF(A1:A307,"1????",F1:F307)
    > > > don't work. Will someone please tell me what I'm doing incorrectly? Thanks
    > > > in advance.


+ 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