Excel 2010 Conditional Sum

Posted on 2012-09-18
Last Modified: 2012-09-18
I recently participated in a question that had a similar "theme", but I can't seem to track it down and I need some assistance in a similar issue.

I have a spreadsheet that contains four columns:  DISC_CODE_GROUP, DISC_MIN, DISC_MAX and DISC_$.  The values under DISC_CODE_GROUP will repeat multiple times within a series of MIN and MAX values.  DISC_$ is the amount of sales at each discount level.

I need to add a column that shows me the % of each row of DISC_$ for the DISC_CODE_GROUP it belongs to.  In the attached example, I manually set my SUM ranges with absolute references, but I would like something that evaluates the DISC_CODE_GROUP and sums the value in DISC_$ if they belong to the same discount group.

I think I need to do a SUMIF/INDEX/MATCH expression, but struggling with the syntax.  (Or maybe this could be done with a pivot table?)
Question by:mark_harris231
    LVL 10

    Author Comment

    To paraphrase the "ask", I'm looking for a formula I can place in Cell E2 and drag down over the full range of my production data without requiring me to change the SUM ranges.
    LVL 19

    Accepted Solution

    in cell F2.... (or replace E2 if you prefer....


    then fill down.  

    (then change cell format of new column to %).
    LVL 10

    Author Closing Comment

    Perfect....and a helluva lot easier than I thought it had to be.  O|o


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvieā€¦
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now