+ Reply to Thread
Results 1 to 8 of 8

Trying to find a sum formula for sales with multiple criteria

  1. #1
    Registered User
    Join Date
    02-26-2016
    Location
    Seattle
    MS-Off Ver
    Excel 2016
    Posts
    6

    Trying to find a sum formula for sales with multiple criteria

    Hello!

    I've been tasked with creating a spreadsheet to track my company's sales leads.
    I have all of it completed besides one section.
    I need to create a formula to show the amount of sales for each sales person for each month and to have it populate on the third tab "Jan".
    I have attached a screenshot of the main tab. Please ignore the song lyrics under cust tab :b I was just plugging in random info to test my formulas.

    I have completed the year to date summary for each lead generator, however I cannot figure out how to get their monthly sales.
    I tried a few different ways but always ended up with a ref error
    I tried this formula and I get a dollar amount but it isn't correct at all
    =SUMPRODUCT(--( $B15='Lead Log'!$E$2:$E$984),(--(1=MONTH('Lead Log'!$O$2:$O$984))),('Lead Log'!$J$2:$J$984))
    Attached Images Attached Images

  2. #2
    Registered User
    Join Date
    02-26-2016
    Location
    Seattle
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Trying to find a sum formula for sales with multiple criteria

    bump because this is my third day of trying formulas and I just can't figure it out

  3. #3
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Trying to find a sum formula for sales with multiple criteria

    Do you have a small sample file? I can not see your image
    If you like my answer please *Add Reputation

  4. #4
    Registered User
    Join Date
    02-26-2016
    Location
    Seattle
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Trying to find a sum formula for sales with multiple criteria

    I've attached what I've been working on.
    It's unfinished by the way. I'm just starting to edit the January tab and need to figure out how to get sales by lead source by month to populate on the Jan tab under $'s sold.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Trying to find a sum formula for sales with multiple criteria

    It would appear that one reason the formula does not give expected results is due to the unfilled cells in 'Lead Log'!$O$2:$O$984 When you run the evaluate formula it shows that all of those unfilled cells are converted to 1's by the formula meaning they will all be considered as having taken place in January. Paste this modified formula in Jan!F15 and drag down:
    Please Login or Register  to view this content.
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    02-26-2016
    Location
    Seattle
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Trying to find a sum formula for sales with multiple criteria

    Thanks for your help. This ALMOST solved the problem. The reason why I selected the O column is because those are the dates the leads were sold, with your formula it is adding up the sum of all leads even if they weren't sold. I just switched it so it says =SUMPRODUCT(('Lead Log'!$E$2:$E$984=B15)*("Jan"='Lead Log'!$N$2:$N$984)*('Lead Log'!$J$2:$J$984)) and it was perfect.
    Not sure why I couldn't get this before but thank you for solving this for me, sometimes it just takes a fresh set of eyes!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Trying to find a sum formula for sales with multiple criteria

    You're welcome and thank your for the feedback. Glad that your issue is resolved. If you haven't already please mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

  8. #8
    Registered User
    Join Date
    02-26-2016
    Location
    Seattle
    MS-Off Ver
    Excel 2016
    Posts
    6

    Re: Trying to find a sum formula for sales with multiple criteria

    Will do, thanks again. This was my first time on the site and I had been stuck on that formula for some time. Have a good one!~

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sales Report - SUM IF based on multiple criteria
    By jpetrini888 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-16-2016, 09:36 AM
  2. Formula to find sales for fixed event at a certain sales date
    By iantix in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-27-2015, 07:56 AM
  3. [SOLVED] Find a date with multiple criteria without using an array formula
    By nikos_tsagos in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-08-2015, 09:46 AM
  4. [SOLVED] Formula to find MAX value based on multiple criteria
    By coach.32 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-27-2014, 02:04 AM
  5. [SOLVED] Trying to find multiple criteria to tag in a formula
    By coach.32 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-22-2013, 11:39 PM
  6. Replies: 1
    Last Post: 09-20-2012, 06:05 PM
  7. Calculate sales forecast using multiple criteria
    By franztupaz in forum Excel General
    Replies: 3
    Last Post: 04-02-2011, 11:45 AM

Tags for this Thread

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