Results 1 to 2 of 2

Thread: Conditional SUMPRODUCT to also ignore blanks
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2005
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Conditional SUMPRODUCT to also ignore blanks

    Hi,

    I have the following code which calculates a weighted average:

    Code:
    =SUMPRODUCT(--($U$2:$U$1203=AE3),$AB$2:$AB$1203,$C$2:$C$1203)/SUMIF($U$2:$U$1203,AE3,$C$2:$C$1203)
    It works very well except that I would like it to ignore blank cells in the range $AB$2:$AB$1203

    I tried to change it to the following code

    Code:
    =SUMPRODUCT(--($U$2:$U$1203=AE3),$AB$2:$AB$1203,(LEN($AB$2:$AB$1203)>0),$C$2:$C$1203)/SUMIF($U$2:$U$1203,AE3,$C$2:$C$1203)
    But this doesn't work and just gives me zero for everything.

    If anyone could let me know how I do this that would be great.

    Many thanks!

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,668
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Help with conditional SUMPRODUCT to also ignore blanks

    The double negative in front of this bit:

    --(U2:U1203=AE3)

    is there to convert a TRUE/FALSE result into a 1 or 0 result. You need the double negative in front of your new TRUE/FALSE test.
    Looking for opportunities

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
  •