+ Reply to Thread
Results 1 to 7 of 7

Sumproduct omitting zeros

  1. #1
    Registered User
    Join Date
    06-21-2017
    Location
    New York NY
    MS-Off Ver
    2013
    Posts
    6

    Sumproduct omitting zeros

    I am trying to create a weighted average by type summary.
    How can I use a sumproduct function that has array criteria of each "type" and omits zero amounts? See attached for dataset.
    Last edited by thenormanizer; 12-14-2017 at 02:34 AM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sumproduct omitting zeros

    Hi, I'm not sure you have attached the correct file. Do you want to calculate eclectic golf scores for the season?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    06-21-2017
    Location
    New York NY
    MS-Off Ver
    2013
    Posts
    6

    Re: Sumproduct omitting zeros

    For context: Type is Unit type for apartments
    Amt is amount rent collected. So 0 means the apartment is vacant
    I want to make a summary by type that has the weighted averages with zeros omitted.

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Sumproduct omitting zeros

    Perhaps I'm missing the point, but where are your "weights"?
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  5. #5
    Registered User
    Join Date
    06-21-2017
    Location
    New York NY
    MS-Off Ver
    2013
    Posts
    6

    Re: Sumproduct omitting zeros

    the weight would be the number of units of each type
    so amount times the number of units in that unit type is the weighted avg rent

  6. #6
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Sumproduct omitting zeros

    May be try

    F2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    G2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    Type
    Amt
    2
    1x1-a1
    0.00
    1x1-a1
    884
    3
    1x1-a250
    887.00
    1x1-a250
    881
    4
    2x2-b250
    932.00
    2x2-b250
    923
    5
    2x2-b250
    935.00
    1x1-a2
    903.5714
    6
    1x1-a2
    930.00
    2x2-b3
    973.75
    7
    1x1-a2
    905.00
    2x2-b2
    997
    8
    2x2-b3
    897.00
    9
    2x2-b3
    1,014.00
    10
    1x1-a2
    867.00
    11
    1x1-a2
    880.00
    12
    2x2-b3
    953.00
    13
    2x2-b3
    960.00
    14
    1x1-a1
    884.00
    15
    1x1-a2
    0.00
    16
    2x2-b2
    997.00
    17
    2x2-b250
    902.00
    18
    1x1-a2
    884.00
    19
    1x1-a250
    875.00
    20
    2x2-b3
    962.00
    21
    2x2-b3
    1,017.00
    22
    1x1-a2
    932.00
    23
    1x1-a2
    927.00
    24
    2x2-b3
    1,023.00
    25
    2x2-b3
    964.00
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  7. #7
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Sumproduct omitting zeros

    Hi all. Use Ankur's column F formula (Post #6) to get a list of unique types, and perhaps the built-in AVERAGEIFS function instead of SUMPRODUCT in column G:
    Please Login or Register  to view this content.
    Last edited by leelnich; 12-14-2017 at 03:02 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Max and Min value between 2 dates omitting zeros and blank
    By Sekars in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-13-2016, 04:10 AM
  2. [SOLVED] Text to Columns is omitting zeros. How do I stop that?
    By jambog in forum Excel General
    Replies: 5
    Last Post: 07-02-2015, 11:26 AM
  3. [SOLVED] Plotting last set of given N numbers by omitting bottom zeros.
    By thilag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2015, 07:14 AM
  4. Frequency function omitting zeros and errors
    By ArekRos in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-04-2012, 06:11 PM
  5. transposing chart, omitting zeros
    By redfur in forum Excel General
    Replies: 1
    Last Post: 04-27-2011, 04:53 PM
  6. Find Min Value omitting blanks or zeros
    By bjohnsonac in forum Excel General
    Replies: 3
    Last Post: 01-06-2011, 12:43 PM
  7. Excel 2007 : Choose minimum value omitting zeros?
    By zaqop4 in forum Excel General
    Replies: 4
    Last Post: 07-16-2010, 07:53 AM

Tags for this Thread

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