Complicated sum required in Excel 2003

Posted on 2011-10-30
Last Modified: 2012-05-12
I am trying to work out the value of sales for each individual sales person per month per nominal code.  

In the attached spreadsheet are the following worksheets:

Sales = Sales of ink cartridges from 01/04/2010 'Nominal_Code' is what splits up certain types of ink cartridges and 'Analysis_1' shows me the individual salesperson who's account bought the said product.

Ink Nominal = This shows me our code for the product and the equivelant nominal code used in the accounts system for the product.

Value = By entering the initials of the sales person in cell B1 should indicate which sales person I want to display the results of.

In 'Value' worksheet under the corresponding dates I would like to display the sales person in cell B1's sales for each individual sales for each product line.

If you need more explaining please let me know.


Question by:Simonrepro
    LVL 50

    Expert Comment

    by:barry houdini
    Hello Simon, I don't see where the Sales dates are? In the attached I added some fictitious dates in N2:N20 of the Sales sheet.

    Assuming that the sales amounts should be quantity multiplied by Unit price (columns F and H) then you could use this formula in C3 copied across and down

    =SUMPRODUCT((Sales!$L$2:$L$100=$B$1)*(Sales!$I$2:$I$100=VLOOKUP($A3,'Ink Nominals'!$A$1:$B$8,2,0)&"")*(TEXT(C$1,"mmm-yy")=TEXT(Sales!$N$2:$N$100,"mmm-yy")),Sales!$F$2:$F$100,Sales!$H$2:$H$100)

    see attached

    regards, barry

    Expert Comment

    You can use Sumproduct to get your total.  I have attached a sample formula for your help.

    See if the file is useful.

    Author Comment

    Hi Barry, the Invoice dates are in the first worksheet and I need to add together all of the sales for each month by nominal code.

    Author Comment

    I just read back through my original message and it's confusing, what i am trying to do is display the sales figures for each month for the salesman in B1 and split by nominal code i.e. so that the results show the total amount for each of the product in cell A.
    LVL 50

    Accepted Solution

    Sorry, don't know how I missed the dates - it's the same formula in C3 copied across and down - except now I used the correct column B rather than fictitious column N. I assumed up to 100 rows of data - of course you probably have more so for real data increase as required

    see revised attachment

    regards, barry

    Author Closing Comment


    I am posting another question on this sheet in a minute

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    755 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

    20 Experts available now in Live!

    Get 1:1 Help Now