+ Reply to Thread
Results 1 to 6 of 6

Formula to calculate total of a series of data based on other factors

  1. #1
    Registered User
    Join Date
    04-24-2015
    Location
    Manchester, England
    MS-Off Ver
    Office 365 2013
    Posts
    7

    Formula to calculate total of a series of data based on other factors

    Hi All,

    I have been trying to solve this problem now for a while and could do with some guidance.

    My sheet contains two lists of users and two sets of values and I need my formula to be able to calculate one of the values (C2, D2) if there name appears in E2 and another if in F2.

    On top of all of that, I need it to only search within a specific date range that is specified in A2 onwards.

    This will generate a report that is split by month.

    I hope I have explained myself sufficiently!

    I have attached a copy of the basic sheet with some test data for you to maybe better understand.

    Any help would be massively appreciated.
    Attached Files Attached Files
    Last edited by neurion; 11-05-2015 at 08:45 AM. Reason: Solved!

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: The impossible formula (I hope not!) :confused:

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: The impossible formula (I hope not!) :confused:

    ... and when you change the thread title, please amend your sheet to show some expected results - your explanation isn't clear enough.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    04-24-2015
    Location
    Manchester, England
    MS-Off Ver
    Office 365 2013
    Posts
    7

    Re: Formula to calculate total of a series of data based on other factors

    Hi Guys,

    Sorry about that! I apologise for my ignorance of the forum rules.

    I'm very much struggling to explain my issue and therefore I wasn't sure of how to title it.

    I have tried to explain more clearly below:

    The user will complete rows A to G with data from an external source.

    I want a formula that completes the following actions:

    • Checks Column A for date ranges that match the month of the year required.
    • Just looking at the month required it should check Column E and G for a users name and calculates the total values in the same row from Column D or F.
    • The results should be displayed on the "Reports" Sheet under the users name and month of the year

    I have attached an updated version with hopefully a clearer explanation.

    Thanks!
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula to calculate total of a series of data based on other factors

    One way:

    =SUMIFS(Form!$D:$D,Form!$A:$A,">="&Report!C$1,Form!$A:$A,"<="&EOMONTH(Report!C$1,0),Form!$E:$E,Report!$A2)+SUMIFS(Form!$F:$F,Form!$A:$A,">="&Report!C$1,Form!$A:$A,"<="&EOMONTH(Report!C$1,0),Form!$G:$G,Report!$A2)

  6. #6
    Registered User
    Join Date
    04-24-2015
    Location
    Manchester, England
    MS-Off Ver
    Office 365 2013
    Posts
    7

    Re: Formula to calculate total of a series of data based on other factors

    That seems to have done the trick!

    Thank you so much for your help

+ 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. Replies: 0
    Last Post: 11-22-2014, 05:02 PM
  2. Replies: 1
    Last Post: 10-03-2012, 10:32 PM
  3. [SOLVED] Macro executes but not giving results :confused::confused::confused:
    By Ganesh7299 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2012, 10:49 AM
  4. Excel 2007 : Hope someone can help with formula
    By Miller60six in forum Excel General
    Replies: 6
    Last Post: 02-23-2009, 08:56 AM
  5. [SOLVED] hope there is a formula
    By Wayne1969 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-28-2009, 11:10 AM
  6. If formula- I hope someone can help me
    By Shazzney in forum Excel General
    Replies: 7
    Last Post: 11-09-2008, 09:57 AM
  7. Help Please! IF formula (i hope that's right!)
    By KarenYM in forum Excel General
    Replies: 2
    Last Post: 06-18-2006, 12:30 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