+ Reply to Thread
Results 1 to 6 of 6

SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range check)

  1. #1
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range check)

    Hi All,

    With reference to the attached cashflow (still in it's embryonic stages at the minute - trying to modify an extremely complicated version I put together a few months ago in a way that makes it much more ergonomic and user friendly).

    Tab 'MASTER' will be where I keep the detail of all expenses, frequency of payment, cost etc.
    Tab 'SUMMARY' attempts to condense this information so the reader (Head Office) can have a clearer, more simplistic view over where the cash is being spent (they can always refer to 'MASTER' should they require more detail).

    In cell D8 of the 'SUMMARY' tab, I want to create a formula that sums the total cost of expenses using the following rules (I will use column D and row 8 as an example but the rules can be extrapolated to the rows and columns below and to the right respectively)
    - cell D8 must sum those expenses on the 'MASTER' spreadsheet who belong to the 'Staff Costs' group
    - of those expenses, cell D8 must only sum those which are expected to crystallise in that given week (the given week shows on row 7 of the 'SUMMARY' tab and ends the day before the date on row 7 in the adjacent column. The date that the expenses are expected to crystallise are given on the 'MASTER' spreadsheet in columns X - NY
    - as such, cell D8 on the 'SUMMARY' tab should currently show a total of $600 because there are two expenses ($350 and $250 respectively) that are classified as 'Staff Costs' on the 'MASTER' spreadsheet, which are expected to be paid on or after 01 January 2016 but before 08/01/2016.

    PS I'm due to catch a flight within 40 minutes so if someone could assist before then, I'll have something to do for the next 7 hours

    EDIT: Apologies, had to change the attachment. The previous one contained specifics. It seems I uploaded the wrong one
    Last edited by STUARTXL; 09-09-2016 at 12:18 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range ch

    Hi,

    Give this a try:

    Please Login or Register  to view this content.
    Hope this is helpful.

    Cheers

  3. #3
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range ch

    Thanks Southward but it doesn't seem to be working. I copied and pasted into cell D8 on 'Summary' tab but the result is still zero. Any reason for this?

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range ch

    Hi,

    You will need to remove your existing formulas first as they are causing a circular reference. Then enter the following formula into D8 and copy as necessary

    =SUMPRODUCT((MASTER!$X$13:$NY$13>=D7)*(MASTER!$X$13:$NY$13<E7)*(MASTER!$W$14:$W$15=$C8)*MASTER!$X$14:$NY$15)
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range ch

    Hi xlnitwit,

    Thanks for the solution, it was working perfectly until I modified another part of my spreadsheet. Now the formula is calculating a value error.

    With reference to the 'MASTER' tab on the attached document, all expenses are displayed day by day from columns R - NS. I want to summarise the expenses on a weekly basis and as such I created the 'Cashflow_Forecast' tab. With specific reference to rows 29 and 30, your formula (or a modification of it) has been inserted in these rows to do just this. You can see that the formula is currently generating a value error. This isn't because your formula is incorrect, it is because of the way the formulae on the 'MASTER' tab in columns R - NS are structured.

    If we replace the formulae in R6 & R7 to a numeric value e.g. 1 and drag across to column NS, then we see that your formulae on the weekly summary tab (that were previously generating a value error), are actually generating the correct result now, hence it definitely is the formula in R6 : NS7 that is the problem. I have identified the specific problem with these formulae too; the very last part of the formula says that if all the preceeding parts of the formula aren't true, then generate a "" value (blank). Previously this blank value was zero (and enabled your formula to generate the correct result), but I was told that it needed to change this zero value to a blank value to enable the count function in the formula to work. So my question to you is, is there any way we can modify your formula to account for this change, so that I can regain my summary of expenses on a weekly basis on the 'Cashflow_Forecast' tab?
    Attached Files Attached Files

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: SUMIFS with vertical criteria and horizontal criteria (horiz. crit. is a date range ch

    Hi again,

    You can alter the formula to
    =SUMPRODUCT((MASTER!$R$5:$NS$5>=C$2)*(MASTER!$R$5:$NS$5<D$2)*(MASTER!$D$6:$D$87=$B29)*IF(ISNUMBER(MASTER!$R$6:$NS$87),MASTER!$R$6:$NS$87,0))
    which must be array entered using Ctl+Shift+Enter and then filled down and across.

+ 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] SUMIFS(?) I need to sum data using both horizontal and vertical criteria
    By bighandsam in forum Excel General
    Replies: 22
    Last Post: 01-06-2017, 11:31 PM
  2. solved
    By ravidesai in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-18-2016, 10:55 AM
  3. SUMIFS (vertical and horizontal with Date range)
    By akul.rkul in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2015, 01:09 AM
  4. Sum with multiple criteria Horizontal and Vertical
    By freqzz in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2015, 04:39 AM
  5. Sum Multiple Criteria horizontal and vertical
    By baronk in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-16-2015, 07:55 AM
  6. [SOLVED] Lookup with vertical and horizontal criteria
    By busygurl in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-23-2014, 12:03 AM
  7. Replies: 5
    Last Post: 06-06-2013, 05:12 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