• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Complicated sum required in Excel 2003

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.

Thanks

Simon
Sales-Commissions2.xls
0
Simonrepro
Asked:
Simonrepro
  • 3
  • 2
1 Solution
 
barry houdiniCommented:
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
27422441.xls
0
 
Sandesh555Commented:
You can use Sumproduct to get your total.  I have attached a sample formula for your help.

See if the file is useful.
Temp.xls
0
 
SimonreproAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SimonreproAuthor Commented:
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.
0
 
barry houdiniCommented:
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
27422441v2.xls
0
 
SimonreproAuthor Commented:
Perfecto!

I am posting another question on this sheet in a minute
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now