+ Reply to Thread
Results 1 to 3 of 3

sumif - calculating a value depending of a list of dates their weeknumber

  1. #1
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    sumif - calculating a value depending of a list of dates their weeknumber

    HI

    I got a table

    where column 1 is a list of sorted dates ( with gaps between the dates )
    column 4 has a list of value's

    I want to collect the values sum of a specific weeknumber.

    I would like to use

    sumif(columnOfDates; weeknumber; columnOfValues)

    sumif ( weeknumber(A5:A10);10; D5:D10) doesn't work. Did try shift control enter but didn't also. Also SumiffS, or I do something wrong actualy I hope.

    Question
    Is weekday not usable as an array function?If yes how to use it?
    How to solve this?


    I can use a second weeknumber column, but like to avoid unneeded column.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: sumif - calculating a value depending of a list of dates their weeknumber

    Most functions are useable as an array, however sumif requires a range, not an array.

    You would need something on the lines of

    =SUMIFS(ColumnOfValues,ColumnOfDates,">="&startofweek,ColumnOfDates,"<="&endofweek)

    Where startofweek and endofweek refer to the dates of the first and last day of the week of interest.
    There is no function in excel to calculate this, so it will require some complex formulas. A second column for weeknumber is the logical choice.

    edit:- this link shows ways to get dates from weeknumbers.

    https://www.ablebits.com/office-addi...k-number-date/
    Last edited by jason.b75; 03-26-2019 at 10:39 AM.

  3. #3
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: sumif - calculating a value depending of a list of dates their weeknumber

    Thank you.

    Is what I was afraid of.

+ 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. Calculating a Bradford score from a list of dates.
    By jcborland in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-30-2018, 09:40 AM
  2. Calculating savings depending on value of columns E & F
    By asavage1984 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-01-2018, 11:32 AM
  3. [SOLVED] List Records Depending on (Due Dates)
    By Subverter in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2016, 03:56 AM
  4. [SOLVED] display weekly dates depending on two variable dates
    By alexander.small in forum Excel General
    Replies: 3
    Last Post: 05-22-2014, 11:30 AM
  5. [SOLVED] Calculating Differences between dates for a Waiting List Calculation
    By richiev86 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2014, 12:42 PM
  6. [SOLVED] Add Weeknumber value to cell depending on the date in a cell 4 columns away
    By Roxner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2013, 02:37 PM
  7. Calculating Amounts per month depending on Start Dates
    By cuadra in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-26-2013, 06:10 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