+ Reply to Thread
Results 1 to 7 of 7

=SUMIFS Formula Question

  1. #1
    Registered User
    Join Date
    03-07-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    =SUMIFS Formula Question

    Good Evening,

    I hope someone can help me here. I have attached my file. Basically what i need to do is put a formula in each cell of "Monthly Overview" that references the correct property and monthly total from the "Monthly Billing"

    Example....
    "Monthly Overview" C2 needs to reference "Monthly Billing" for what was billed to Apex Broadcasting (B column) in Jan (C Column)

    Please let me know if you have any questions and thank you in advance
    Attached Files Attached Files
    Last edited by Jsimpson19; 12-10-2013 at 11:02 PM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: =SUMIFS Formula Question

    hi Jsimpson. first of all, change all your dates in row 1 to make it real dates & not texts. type in jan2014 in C1 of Month Overview. drag across to get Jan-Dec. you can right-click & format the cells to show just Jan by going to Custom:
    mmm

    then in C2:
    =SUMIFS('Monthly Billing'!$D:$D,'Monthly Billing'!$B:$B,$B2,'Monthly Billing'!$C:$C,C$1)

    the 1st range should just be the Amount you wish to sum up & not B:D. the criteria can be referred to cells instead of typing it

    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

  3. #3
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: =SUMIFS Formula Question

    I hope this helps
    Attached Files Attached Files

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,356

    Re: =SUMIFS Formula Question

    Another approach is sumproduct
    **can't use whole column for range so you have to adjust the range...

    =SUMPRODUCT(--('Monthly Billing'!$B$2:$B$1000=$B2),--(MONTH('Monthly Billing'!$C$2:$C$1000)=COLUMN(A$1)),'Monthly Billing'!$D$2:$D$1000)

    drag right then down.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  5. #5
    Registered User
    Join Date
    03-07-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: =SUMIFS Formula Question

    thank you so much. i just have one questions. Since everything is linked together how come i have the following:

    Monthly Overview Jan - 671,299.89
    Monthly Billing Jan - 688,742.89
    Totals Jan - 688,742.89

    Shouldn't they all match?
    Last edited by Jsimpson19; 12-09-2013 at 11:17 PM.

  6. #6
    Registered User
    Join Date
    03-07-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: =SUMIFS Formula Question

    maybe the sumsif is not the correct formula to use...

    do you suggest another one?

  7. #7
    Registered User
    Join Date
    03-07-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: =SUMIFS Formula Question

    Thank you so much for all your help...i think i might be done with this workbook now

+ 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] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  2. Sumifs question
    By RussellNonBrand in forum Excel General
    Replies: 3
    Last Post: 11-08-2011, 02:05 PM
  3. Excel 2007 : sumifs question
    By MiserableLawStdnt in forum Excel General
    Replies: 1
    Last Post: 10-12-2011, 10:23 AM
  4. SUMIFS question
    By ddsouza in forum Excel General
    Replies: 1
    Last Post: 03-04-2011, 02:28 PM
  5. Sumifs question.
    By antsimsjr in forum Excel General
    Replies: 1
    Last Post: 05-28-2010, 11:04 AM

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