+ Reply to Thread
Results 1 to 4 of 4

SumLookup formula with multiple criteria

  1. #1
    Registered User
    Join Date
    11-12-2019
    Location
    Darlington, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    SumLookup formula with multiple criteria

    Hi,
    Thanks for looking. I am wanting to track some employee recruitment costs month on month, as the company policy is that the recruitment costs are spread over three months.
    I have no doubt that someone will want to know the costs and the split by cost center so that is why I am wanting to track it. The information in the Detail tab is the journal that I load into the finance system, so if I can use this as a basis for the information that would be good as I can just drop the information in here once a month and update the summary.
    What I am looking for is a formula in the summary tab that will lookup the persons name, against the period and a particular nominal code (picking up all nominal codes will return values of zero). I only have Octobers information at the minute.
    I will the build this file over the next year.
    Thanks in advance.
    Last edited by PierremontQuaker03; 11-26-2019 at 04:53 PM. Reason: Title amend

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

    Re: SumLookup formula with multiple criteria

    this looks like it should work... =SUMIFS(Detail!$F$1:$F$27,Detail!$E$1:$E$27,Summary!C$3,Detail!$A$1:$A$27,Summary!$B4)
    the only problem is that for your example you show October 31 2019 then in the next column you have November 1 2019 so will the dates in the detail tab all be either the first of the month or last of the month because if you are going to have multiple dates for any given month then the formula will need to be changed.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    11-12-2019
    Location
    Darlington, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: SumLookup formula with multiple criteria

    Quote Originally Posted by Sambo kid View Post
    this looks like it should work... =SUMIFS(Detail!$F$1:$F$27,Detail!$E$1:$E$27,Summary!C$3,Detail!$A$1:$A$27,Summary!$B4)
    the only problem is that for your example you show October 31 2019 then in the next column you have November 1 2019 so will the dates in the detail tab all be either the first of the month or last of the month because if you are going to have multiple dates for any given month then the formula will need to be changed.
    Great thank you, I moved it to look up the period number instead of the date - so that will hopefully solve that. One other thing - the outcome of the formula is zero as there is no reference to the Nominal column (Detail tab, column C). I can delete half the journal but is there another way where I can select the nominal code to use?

  4. #4
    Registered User
    Join Date
    06-26-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    26

    Re: SumLookup formula with multiple criteria

    The easiest way to achieve what you are looking for is to use a pivot table.

    Please see the attached.

    Hope it helps

    Green Aardvark

+ 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] Retrieving multiple text strings based on multiple criteria (formula update)
    By McKneezy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2019, 08:52 PM
  2. Multiple criteria formula with date as criteria
    By Phlegon_of_Tralles in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-06-2019, 09:27 PM
  3. Replies: 4
    Last Post: 04-14-2017, 05:14 PM
  4. [SOLVED] Formula to count occurrences, multiple ranges, multiple criteria, with wildcard
    By TMMc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-23-2017, 03:27 PM
  5. Formula to Sum 3 Column Values based on Multiple Criteria for Multiple Rows
    By RMerckling in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2016, 07:46 PM
  6. Formula for summing on multiple row criteria and a dynamic column criteria
    By ianswilson815 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-25-2016, 01:58 PM
  7. Replies: 1
    Last Post: 05-16-2011, 05:00 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