+ Reply to Thread
Results 1 to 5 of 5

Trying to create formula to calculate spend per week from a month of data.

  1. #1
    Registered User
    Join Date
    10-15-2020
    Location
    UK
    MS-Off Ver
    365
    Posts
    2

    Trying to create formula to calculate spend per week from a month of data.

    Hi,

    I have done some basic formulas before, but I'm really struggling to get this one to work.

    I have a sheet with a list of job numbers, cost values and dates.
    I have a separate sheet in the same workbook to calculate the spend of each job number. I have calculated the total monthly spend with the formula below.
    =SUMIF('SITE ORDERS'!$B$3:$B$999, B3, 'SITE ORDERS'!$I$3:$I$999)
    =SUMIF(Range, Criteria, Sum range)
    =SUMIF(Range of job numbers, Job number, range of order values)

    I have gotten so far with the formula to work out the value per week

    =SUMIF('SITE ORDERS'!$I$3:$I$999,B3,'SITE ORDERS'!$B$3:$B$999,'SITE ORDERS'!$K$3:$K$999,">="&D1,"<"&E1)
    =SUMIF('range of order values, job number, range of job numbers, range of dates, equal or greater than 1st of month, less than 8th of month.


    Any input would be appreciated...

    Bosy
    Attached Images Attached Images
    Last edited by bosy9989; 10-15-2020 at 11:13 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    952

    Re: Trying to create formula to calculate spend per week from a month of data.

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Trying to create formula to calculate spend per week from a month of data.

    SUMIF can only be used for a single criterion, so you will need to use SUMIFS, as this will take multiple criteria. The syntax is a bit different than for SUMIF - essentially:

    SUMIFS(range of values to sum, criterion_1_range, criterion_1, criterion_2_range, criterion_2 ...

    where you have pairs of criteria_ranges and the criteria following after the sum_range.

    In your case I think you will need to do this:

    =SUMIFS('SITE ORDERS'!$I$3:$I$999,'SITE ORDERS'!$B$3:$B$999,B3,'SITE ORDERS'!$K$3:$K$999,">="&$D$1,'SITE ORDERS'!$K$3:$K$999,"<"&$E$1)

    I've used 3 different colours to highlight the three different criteria pairs.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    10-15-2020
    Location
    UK
    MS-Off Ver
    365
    Posts
    2

    Re: Trying to create formula to calculate spend per week from a month of data.

    Hi Pete,

    That sorted it although I had to do ">=&$D$1" for the date criteria, as opposed to ">="&$D$1. But its working great, thanks a lot.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Trying to create formula to calculate spend per week from a month of data.

    Glad to help, and thanks for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

+ 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] Calculate a YTD Total based on Reporting Month and Week of Month via drop down selections
    By Miskondukt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-02-2018, 05:14 PM
  2. [SOLVED] I'm lost here. Formula to calculate % per day of the week, month 100%
    By dceremuga in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-02-2016, 09:44 AM
  3. [SOLVED] Calculate Week Range from Week Number, Month and Year?
    By A108A108 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-07-2016, 03:11 PM
  4. Replies: 10
    Last Post: 03-15-2016, 05:16 PM
  5. Replies: 2
    Last Post: 06-05-2013, 02:05 AM
  6. [SOLVED] Formula For Sum of Spend Per Month During a Start and End Date
    By t_man000 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-12-2013, 09:13 AM
  7. Replies: 8
    Last Post: 08-10-2009, 04:21 PM

Tags for this Thread

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