+ Reply to Thread
Results 1 to 5 of 5

Single cell array or sumproduct formula

  1. #1
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Single cell array or sumproduct formula

    To calculate the result for a month is easy, but I can't figure how to get a single cell formula to calculate for the year.

    The sample attached explains it better.

    Thanks for any ideas.
    Attached Files Attached Files
    Last edited by BRISBANEBOB; 06-16-2009 at 06:26 PM.

  2. #2
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193

    Re: Single cell array or sumproduct formula

    Please be more specific, what exactly are you wanting accomplished? A sum of the products of the percentages (until March) and what? 20000?
    starryknight64

  3. #3
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Re: Single cell array or sumproduct formula

    Each month has the potential to incur a penalty. I'm trying to sum up the penalties for the year. Effectively this means every month from January thru December.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Single cell array or sumproduct formula

    I assume that over 90% incurs no penalty, try this formula for the whole year

    =SUMPRODUCT(--(F11:Q11<>""),LOOKUP(F11:Q11,{0,0.8,0.850001,0.900001;30000,20000,10000,0}))

  5. #5
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    807

    Re: Single cell array or sumproduct formula

    Thanks - works perfectly. i was missing the <>"" check.

    Much appreciated

+ 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