Results 1 to 6 of 6

Thread: Sumproduct help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sumproduct help

    Hello

    I am trying to accomplish a sumproduct sort of thing and almost have it but not quite yet.

    I have cobbled together this formula...

    =SUMPRODUCT(IFERROR((G10:G8841>I4)*(H10:H8841="Auto")*(I10:I8841>0),)) array entered.

    At issue is the fact that cells in column G may contain numbers (a good thing) or text (a bad thing), How do I ignore the cells that have text or only count the numerics. As it stands now, the text cells are being counted (a bad thing).

    Thanks!

  2. #2
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,417
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sumproduct help

    Will the following formula work for you?

    =COUNTIFS(G10:G8841,">"&I4,H10:H8841,"Auto",I10:I8841,">0")

  3. #3
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,730
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sumproduct help

    Try:

    =SUMPRODUCT(IFERROR((G10:G8841+0>I4)*(H10:H8841="Auto")*(I10:I8841>0),))

    When you perform an arithmetic function on a number saved as a string, or a regular number, you get a number. If you try to add 0 to a text value, you get an error. In this case, your IFERROR will catch that error and turn it to a 0.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,986
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Sumproduct help

    Continuing with your formula

    =SUMPRODUCT((ISNUMBER(G10:G8841))*(G10:G8841>I4)*(H10:H8841="Auto")*(I10:I8841>0))
    Last edited by DanteAmor; Oct 10th, 2019 at 08:07 PM.
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Jan 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumproduct help

    Perfect! Thank you

    Quote Originally Posted by Eric W View Post
    Try:

    =SUMPRODUCT(IFERROR((G10:G8841+0>I4)*(H10:H8841="Auto")*(I10:I8841>0),))

    When you perform an arithmetic function on a number saved as a string, or a regular number, you get a number. If you try to add 0 to a text value, you get an error. In this case, your IFERROR will catch that error and turn it to a 0.

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,730
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sumproduct help

    Glad we could help!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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