+ Reply to Thread
Results 1 to 2 of 2

SUMIFS to go to another worksheet and calculate Commision

  1. 07-11-2018, 04:48 PM #1
    lougs7
    lougs7 is offline
    Forum Contributor
    Join Date
    09-23-2015
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    187

    Wink SUMIFS to go to another worksheet and calculate Commision

    Hi all,

    Hello wonderfull ExcelForum members. I am reaching out to you guys because I am stuck on an Excel formula. I have 2 tasks to do. There is a sample sheet attached for your reference. All the sensitive info was deleted, please don't mind all the blank spaces - real data is indeed there so keep the positioning of the Sheets.

    Task 1 I am stuck on:
    I am trying to get a SUMIFS (or another function if more efficient) to go to another worksheet and calculate Commission dollars per Sales Rep for the month, and also year to date. I have a =TODAY() formula in G2 on 'Total Comission' where the formula can identify the Year & Month.

    Task 2 I am stuck on
    Additionally to that, It would be wonderful to input a custom date range as you sheet on 'Total Commission' where the user can manually put the dates he wants to see and the commissions would get added up.

    Anything would help me, I have spent a few too many hours on this so would appreciate your help.

    Kindly attach the Sample workbook with the forumla's if you happen to figure it out.

    Cheers!

    Lucas
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-12-2018, 02:42 AM #2
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: SUMIFS to go to another worksheet and calculate Commision

    hey Lucas. next time, try inputting the cell reference you want the answer on and manually key in the desired answer too. that helps us understand more easily. you can look at my signature for examples.

    I am guessing in cell G10:
    =SUMIFS(test!$O:$O,test!$A:$A,">="&EOMONTH($G$2,-1)+1,test!$A:$A,"<="&EOMONTH($G$2,0),test!$J:$J,$E10)

    cell H10:
    =SUMIFS(test!$O:$O,test!$A:$A,">="&DATE(YEAR($G$2),1,1),test!$A:$A,"<="&EOMONTH($G$2,0),test!$J:$J,$E10)
    or:
    =SUMIFS(test!$O:$O,test!$A:$A,">="&DATE(YEAR($G$2),1,1),test!$A:$A,"<="&$G$2,test!$J:$J,$E10)
    the first one sums to the month end of today's date. the 2nd sums up to today's date (i suppose you won't have dates beyond today?)

    cell K10:
    =SUMIFS(test!$O:$O,test!$A:$A,">="&$K$7,test!$A:$A,"<="&$K$8,test!$J:$J,J10)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SumIF works, SumIFS doesn't
    By jomili in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-13-2018, 11:27 AM
  2. SUMPRODUCT/SUMIFS Dynamic array doesn't works
    By regoroes in forum Excel General
    Replies: 5
    Last Post: 01-04-2018, 03:01 AM
  3. Does Sumifs function works on several column for calculation?
    By Sid_2987 in forum Excel General
    Replies: 3
    Last Post: 03-31-2017, 07:21 AM
  4. Make Timer function more efficient
    By mat83400 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-05-2016, 09:20 AM
  5. SUM function works...SUMIF & SUMIFS do not. Please help.
    By archi2this in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2015, 07:29 AM
  6. [SOLVED] SumIfS works fine for 2 conditions, but when I try to add a third, #value appears ??
    By dredwolf in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-18-2012, 04:56 PM
  7. Excel 2007 : SUMIFS works for returning numbers, but what do you use for text?
    By Rick K in forum Excel General
    Replies: 7
    Last Post: 08-31-2011, 03:16 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1