+ Reply to Thread
Results 1 to 9 of 9

"Sum Product" not identifying elements correctly.

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    "Sum Product" not identifying elements correctly.

    Attached BUDGET sheet contains 100 records covering five Cost Centres for four Departments over Jan - May 18

    "P & L" sheet should work as follows:

    1. From the DropList in E5, User selects either "All" or an individual Department.

    2. From the DropList in E6, User selects a month between Jan - May 18

    Following formulae should then show the budget for each Cost Centre against those two criteria:

    Monthly formulae:

    "=IF($E$5="All",SUMPRODUCT(BUDGET!$E$2:$E$101*(BUDGET!$A$2:$A$101=$B9)*(BUDGET!$D$2:$D$101=$E$6)),SUMPRODUCT(BUDGET!$E$2:$E$101*(BUDGET!$A$2:$A$101=$B9)*(BUDGET!$G$2:$G$101=$E$5)*(BUDGET!$D$2:$D$101=$E$6)))"

    (If E5 says "All" then find the cost in Col E where the Cost Centre in Col A matches B9 and the Month in Col D matches E6.
    But if E5 is a "Department", then find the cost in Col E where the Cost Centre in Col A matches B9, the Month in Col D matches E6, AND the Department in Col G matches E5).

    YTD Formulae:
    =IF($E$5="All",SUMPRODUCT((BUDGET!$D$2:$D$101>=DATE(YEAR($E$6),1,1))*(BUDGET!$D$2:$D$101<=DATE(YEAR($E$6),MONTH($E$6),1))*(BUDGET!$A$2:$A$101=$B9),BUDGET!$E$2:$E$101),SUMPRODUCT((BUDGET!$D$2:$D$101>=DATE(YEAR($E$6),1,1))*(BUDGET!$D$2:$D$101<=DATE(YEAR($E$6),MONTH($E$6),1))*(BUDGET!$G$2:$G$101<=D$5)*(BUDGET!$A$2:$A$101=$B9),BUDGET!$E$2:$E$101))

    (If E5 ="All", find the cost in Col E where the Cost Centre in Col A matches B9 and the Month in Col D is between 1 Jan and the month in E6.
    But if E5 is a "Department", then find the cost in Col E where the Cost Centre in Col A matches B9, the Month in Col D is between 1 Jan and the month in E6, AND the Department in Col G matches E5).

    PROBLEM:
    Formula works if E5 ="All" and Month ="Jan 18", Both Month and YTD calculate correctly.
    But if you choose a different month, Month's Budget is blank, but the YTD calculates

    And if E5=a Department and Month= Jan , Col G shows the Month's Budget but the YTD budget in Col K is blank
    And if E5=a Department and Month= Feb to May, nothing calculates

    Hope someone can see the flaw in the formulae logic, and all corrections, suggestions and alternative solutions welcomed as ever.

    Ochimus
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,353

    Re: "Sum Product" not identifying elements correctly.

    The SUMPRODUCT itself is not the problem.

    Your problem with the month is here:

    Excel 2016 (Windows) 32 bit
    M
    N
    1
    Month
    2
    Jan-18
    01/01/2018
    3
    Feb-18
    02/02/2018
    4
    Mar-18
    06/03/2018
    5
    Apr-18
    07/04/2018
    6
    May-18
    09/05/2018
    Sheet: BUDGET

    You neeed to change all of these in column M to the first of the month in question.

    The formula in K9 should be this:

    =IF($E$5="All",SUMPRODUCT((BUDGET!$D$2:$D$101>=DATE(YEAR($E$6),1,1))*(BUDGET!$D$2:$D$101<=DATE(YEAR($E$6),MONTH($E$6),1))*(BUDGET!$A$2:$A$101=$B9),BUDGET!$E$2:$E$101),SUMPRODUCT((BUDGET!$D$2:$D$101>=DATE(YEAR($E$6),1,1))*(BUDGET!$D$2:$D$101<=DATE(YEAR($E$6),MONTH($E$6),1))*(BUDGET!$G$2:$G$101=E$5)*(BUDGET!$A$2:$A$101=$B9),BUDGET!$E$2:$E$101))
    Attached Files Attached Files
    Last edited by AliGW; 03-24-2018 at 03:41 AM. Reason: Fixed typo.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: "Sum Product" not identifying elements correctly.

    Try with in "K9"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    copy down

    Secondly why not using simple Pivot Table. Its more convincing.
    Last edited by avk; 03-24-2018 at 03:36 AM. Reason: addition


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,353

    Re: "Sum Product" not identifying elements correctly.

    Not everybody likes using pivot tables: some find them easy, others prefer a formula approach. What do you mean by 'more convincing'? The data tells a story, whether presented as a pivot table or not.

  5. #5
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: "Sum Product" not identifying elements correctly.

    Oh i am sorry, i say to that, easy instead of convincing.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,353

    Re: "Sum Product" not identifying elements correctly.

    Thanks for the clarification. Pivot tables are only easy if you know how. If you think it would be better done using a pivot table, then explain how it should be done: don't assume that everybody knows.

  7. #7
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: "Sum Product" not identifying elements correctly.

    Refer attach file. (Sheet "Pivot")
    Firstly in "Budget sheet in ytd column calculate ytd with simple formula : In "F2"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In "F3"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Select data from "Budget" sheet.
    Go to "Insert" Tab > "Pivot Table" > "New Worksheet" > "OK"
    In "Report Filter" : "Department" & "Month"
    In "Row Labels" : "Code"
    In "Column Labels" : "Vales"
    In "Vales" : "Month Value" & "YTD"
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    Re: "Sum Product" not identifying elements correctly.

    Many thanks to both for the prompt constructive advice and examples, which I fed into the "real" file and now have a Happy User.

    I used the formula pointers, as the structure of the "real" worksheet the data was populating meant a Pivot Table could not be used, but it is difficult to "specify" in a question all the constraints on which approach can or can't be followed.

    Marking this as "Solved" and looking forward to the next challenge.

    Ochimus

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,353

    Re: "Sum Product" not identifying elements correctly.

    Glad to have helped!

+ 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] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  2. Replies: 7
    Last Post: 11-08-2015, 03:57 PM
  3. Replies: 5
    Last Post: 06-12-2015, 07:02 PM
  4. [SOLVED] Assigning a 2D Array to a bigger size range results in "#N/A" in the uncovered elements
    By TopXorGuy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2013, 10:45 AM
  5. Replies: 0
    Last Post: 01-09-2013, 06:52 PM
  6. Replies: 0
    Last Post: 08-18-2010, 02:17 PM
  7. [SOLVED] Excel: How to import multiple XML "Repeating child elements" same.
    By l8vj in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-12-2006, 08: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