+ Reply to Thread
Results 1 to 8 of 8

SUMIFS question

Hybrid View

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    48

    SUMIFS question

    I have the following type of list:

    col A col B col C col D col E
    Date Project Start Time Stop Time Total Hours
    8/1 XYZ 10:00 13:00 3.0
    8/2 XYZ 09:00 09:30 0.5
    8/3 ABC 11:00 11:45 0.75

    I currently have the formula to pull out the total hours for the particular project ... =SUMIFS($E$4:$E$504,$B$4:$B$504,"XYZ") ....
    however, what I'm looking for is to be able to grab the same data but for a particular week (i.e., like weeknum?). I've included a copy of the file so you can see the actual columns, etc.

    QA Production.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: SUMIFS question

    So you want to update the formulas in Rows 15-18 to include monthly totals and Rows 21-24 to include weekly totals, correct? Also, would these be calendar totals or would they be trailing totals (e.g., month of August vs the last 30 days)?

  3. #3
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: SUMIFS question

    For weeknum as a criteria you could use sumproduct like below you can refer a cell range inplace of weeknum (in this case 35)

    Formula: copy to clipboard
    =SUMPRODUCT(--(WEEKNUM(L4:L504+0)=35),--(M4:M504="Scorecard"),(Q4:Q504))
    Click just below left if it helps, Boo?ath?

  4. #4
    Registered User
    Join Date
    09-27-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: SUMIFS question

    Boopathiraja,
    Thanks. Now, if I want to make that weeknum (i.e., 35) dynamic, so it looks at what ever the current week is (based on the date entered).

  5. #5
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: SUMIFS question

    Try with the below, say yout date is in D6

    Formula: copy to clipboard
    =SUMPRODUCT(--(WEEKNUM(L4:L504+0)=WEEKNUM(D6)),--(M4:M504="Scorecard"),(Q4:Q504))

  6. #6
    Registered User
    Join Date
    09-27-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: SUMIFS question

    thanks boopathiraja. looks like I'll have to set a cell to the current weeknum & then use the criteria based off of that. thanks again.

  7. #7
    Registered User
    Join Date
    09-27-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: SUMIFS question

    Hoyasaxa215, The daily & monthly totals (stats areas) are fine as they are.
    These are calendar tots.
    Last edited by DonW; 08-29-2014 at 05:58 PM.

+ 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. SUMIFS Question
    By dneid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-13-2012, 05:28 PM
  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