+ Reply to Thread
Results 1 to 4 of 4

SUM function with 2 IF variables

  1. #1
    Registered User
    Join Date
    06-15-2015
    Location
    Birmingham, England
    MS-Off Ver
    Microsoft Office 2013
    Posts
    5

    SUM function with 2 IF variables

    I’m trying to create a spreadsheet for work in which we allocate each other a certain amount of documents to process every few minutes. We are currently using a paper system where we write it down, however, I’m trying to create a spreadsheet to make this faster and more automated, and to not waste paper.
    I’m a little stuck trying to figure out a function for the ‘Totals’ worksheet, which will sum up how much is assigned to a person based on a specific day.
    In my spreadsheet I have manually input some of the totals into the ‘Totals’ worksheet, to give you an example of what I want to be achieved.

    Hopefully, you guys might understand what I’m trying to achieve, but please reply if I’m being a little vague.


    Also, is there a way of having the dates for the ‘Totals’ section automatically generate (without duplicates) from the dates in the ‘Allocation’ worksheet? So that when someone creates a new date in the ‘Allocation’ worksheet, it will automatically appear (hopefully in chronological order) in the dates section for the ‘Totals’ worksheet.

    Allocation Spreadsheet - Dummy.zip

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: SUM function with 2 IF variables

    i cannot open zip files from work, so based on your title, have you tried sumifs? it is =sumifs(sum range, criteria range 1, criteria 1, criteria range 2, criteria 2, etc.) Or it can be sumif(range, criteria, sum range)+sumif(range, criteria, sum range).
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: SUM function with 2 IF variables

    b2=SUMIFS(Allocation!$G$2:$G$100,Allocation!$B$2:$B$100,$A2,Allocation!$E$2:$E$100,B$1)
    Please Login or Register  to view this content.
    Try this and copy across
    I did it for 100 rows change the range as per your needs edit the 100 as 500 / 1000 / 10000 etc.,
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    06-15-2015
    Location
    Birmingham, England
    MS-Off Ver
    Microsoft Office 2013
    Posts
    5

    Re: SUM function with 2 IF variables

    Thanks guys, I looked into SUMIF formulas and managed to work out a formula which seems to work perfectly (so far).
    I also managed to get the dates right by using an array formula.

    One further question, is there any way of creating an array formula which covers multiple cells? The array formula I got works fine, but I have to drag it manually to cover the many cells I want it to cover. Is there a more efficient way of pasting the array formula over many cells with the same result as dragging the formula manually?

+ 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. if function 3 variables
    By Woddow in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2015, 09:59 PM
  2. New function for more variables
    By Alenn in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-10-2014, 12:38 PM
  3. [SOLVED] IF function with three variables
    By freethinker in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-14-2013, 07:03 AM
  4. IF function for 2 or more variables
    By speakerbruce in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-29-2013, 01:33 PM
  5. Replies: 3
    Last Post: 09-11-2012, 02:03 AM
  6. 2 variables in a function
    By unclesnoop in forum Excel General
    Replies: 3
    Last Post: 01-28-2011, 10:58 AM
  7. Excel 2007 : Function with three variables
    By SWPRINT in forum Excel General
    Replies: 2
    Last Post: 11-02-2010, 12:25 PM

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