+ Reply to Thread
Results 1 to 7 of 7

table formula sum product not working

Hybrid View

  1. 02-03-2022, 12:19 PM #1
    TheCoachHank
    TheCoachHank is offline
    Registered User
    Join Date
    02-03-2022
    Location
    United States
    MS-Off Ver
    MS office 2016
    Posts
    5

    table formula sum product not working

    I built a table to pull data and it works on my current sheet. i used this formula =SUMPRODUCT((table3[12 - How many appointments did you schedule today?])*(table3[1 - Hi, what''s your first name?]=$A2)*(table3[3 - What date are you reporting numbers for?]=$C2)*(table3[2 - Last name?]=$B2)) it pull the data over.

    When i added new data i get this =SUMPRODUCT((#REF!)*(#REF!=$A2)*(#REF!=$C2)*(#REF!=$B2)) I did change the table name as well but that didn't change anything.


    When i copy the working formula into to the sheet above with the #ref with updated data this is what i get.
    =SUMPRODUCT(('Copy of Copy of Weekending Goal Preformance V.xlsx'!table3[12 - How many appointments did you schedule today?])*('Copy of Copy of Weekending Goal Preformance V.xlsx'!table3[1 - Hi, what''s your first name?]=$A2)*('Copy of Copy of Weekending Goal Preformance V.xlsx'!table3[3 - What date are you reporting numbers for?]=$C2)*('Copy of Copy of Weekending Goal Preformance V.xlsx'!table3[2 - Last name?]=$B2))

    Also is there a way to have the sheet pull the dates vs. me changing them manually weekly.

    Thank you for your help in advance.
    Reply With QuoteRegister To Reply
  2. 02-03-2022, 12:25 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,371

    Re: table formula sum product not working

    Welcome to the forum.

    Isw this an Access or Excel question? If the latter, I will move the thread for you.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 02-03-2022, 12:31 PM #3
    TheCoachHank
    TheCoachHank is offline
    Registered User
    Join Date
    02-03-2022
    Location
    United States
    MS-Off Ver
    MS office 2016
    Posts
    5

    Re: table formula sum product not working

    Excel Question
    Reply With QuoteRegister To Reply
  4. 02-03-2022, 12:45 PM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,371

    Re: table formula sum product not working

    Thanks. Please update your forum profile as requested in my first post to this thread. We are not really interested in your Windows version.

    I am waiting for your workbook to be attached.
    Reply With QuoteRegister To Reply
  5. 02-03-2022, 02:07 PM #5
    TheCoachHank
    TheCoachHank is offline
    Registered User
    Join Date
    02-03-2022
    Location
    United States
    MS-Off Ver
    MS office 2016
    Posts
    5

    Re: table formula sum product not working

    I will upload it shortly, I updated the Version to 2016. been on calls back to back..
    Reply With QuoteRegister To Reply
  6. 02-03-2022, 02:29 PM #6
    TheCoachHank
    TheCoachHank is offline
    Registered User
    Join Date
    02-03-2022
    Location
    United States
    MS-Off Ver
    MS office 2016
    Posts
    5

    Re: table formula sum product not working

    fILE UPLOAD thank you for your help!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 02-08-2022, 11:33 AM #7
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,855

    Re: table formula sum product not working

    Except in the first part of the formula, the Structured References will need to be locked as in: =SUMPRODUCT((table[12 - How many appointments did you schedule today?])*(table[[1 - Hi, what''s your first name?]:[1 - Hi, what''s your first name?]]=$A1)*(table[[3 - What date are you reporting numbers for?]:[3 - What date are you reporting numbers for?]]=$C1)*(table[[2 - Last name?]:[2 - Last name?]]=$B1))
    This video tutorial may help: https://www.youtube.com/watch?v=gZstykyq3Ns&t=89s
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Aggregate PIVOT TABLE with custom Cumulative Product formula
    By do34 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-24-2020, 12:55 AM
  2. [SOLVED] Sum Product ( Count if working good, but I need help with dragging formula.
    By Brian.Aerojet in forum Excel General
    Replies: 7
    Last Post: 01-11-2018, 12:40 PM
  3. [SOLVED] Sum Product Not Working
    By Statz in forum Excel General
    Replies: 4
    Last Post: 11-18-2016, 07:14 AM
  4. Product formula that requires 2 Ingredients to create 1 Product and sort droplist.
    By Teknoslasher in forum Excel General
    Replies: 9
    Last Post: 10-16-2014, 07:46 AM
  5. [SOLVED] Sum Product not working
    By pauldaddyadams in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-01-2013, 07:36 AM
  6. Formula to Calculate Optional Color Cost Based on Size of Product Selected Not Working
    By Hube2002 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-15-2012, 12:11 AM
  7. formula for product not working
    By steve in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-04-2006, 03:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1