Results 1 to 2 of 2

Thread: Sumproduct help!

  1. #1
    Neophyte noviceuser's Avatar
    Join Date
    Jun 2019
    Posts
    1
    Articles
    0
    Excel Version
    excel 2010

    Sumproduct help!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am wanting to return information based on certain criteria being met.

    I have used SUMPRODUCT and COUNTIF for each separate stage, but not linked them together and I have hit a brick wall.

    What I am trying to achieve is,

    1, Filter out the information by month (this is an annual sheet)
    2, Lift how many vans a person has sold split into new and used (P-O), as in COUNTIF "BE" column K if E is not blank, sumproduct column U into summary tab - E5 - Sales Rev, repeat for column V into summary F5 - profit. But I need to discount if column O (workshop costs) is blank.

    I have been doing this so far by manually filtering the new and p-o,copying onto a separate workbook, then sourcing the information. This is no longer practicable.

    as this document is constantly updated I wanted to have this as an automatic summary and would like to avoid using a VBA / macro if at all possible. (these have not worked well in our workplace before).

    sorry if this makes no sense.

    Sample Van sales.xlsx

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,551
    Articles
    0
    Excel Version
    365
    You can do most of this with pivot tables. See Summary sheet, 2 tables at cells B21 and B36.
    I added a New/Used column to the 2019-2020 sheet (column AL) and the manufacturers table needed a new Manufacturer column (AP) to be added to that sheet, being just the first word of the Model column.
    I don't know how to identify SOR vans.
    The first table is sorted by the salesperson with the biggest profit, and the second table sorted by the manufacturer with the biggest profit.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •