• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Sumproduct formula for filtered Pivot table getting #Ref! error|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Sumproduct formula for filtered Pivot table getting #Ref! error|General Excel Questions & Answers|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Sumproduct formula for filtered Piv…
sp_PrintTopic sp_TopicIcon
Sumproduct formula for filtered Pivot table getting #Ref! error
Avatar
MITUL PARIKH
Member
Members
Level 0
Forum Posts: 68
Member Since:
December 8, 2017
sp_UserOfflineSmall Offline
1
April 30, 2018 - 7:27 am
sp_Permalink sp_Print sp_EditHistory

Hello Friend;

Good Morning;

In file attached , on worksheet named  Piv. Tab. -Filter by item type ,Is it possible to  write Sumproduct  formula  to get total for  "Excel books & "Excel"   Pamphlets, "Word" Books & "Word" pamphlets, etc.  If not,  what is correct formula to write to extract data from that Piv. Tab?

Also,  on Sheet 1 , is it OLAP Piv. Tab?.  because I tried very   hard to get correct answer by writing GetPivotData formula for "Beverages",but  keep getting #Ref! error, so what is correct GPD formula ?
   

Thank  you very much.

Have a great day.
          

Sincerely;

Mitul

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
April 30, 2018 - 10:10 am
sp_Permalink sp_Print

Hi Mitul,

You can use SUMPRODUCT, but your source data is a mess. i.e. some "Excel" Book strings have leading spaces and others don't. Likewise for Word. You should clean these leading spaces from your source data.

See file attached with SUMPRODUCT for the Excel books and pamphlets. You'll notice it is missing some values for "Excel" Book that has the leading spaces.

And yes, the PivotTable on sheet 1 is an OLAP or Power Pivot PivotTable so the GETPIVOTDATA function syntax is slightly different. I cover this in my Power Pivot course here.

Mynda

Avatar
MITUL PARIKH
Member
Members
Level 0
Forum Posts: 68
Member Since:
December 8, 2017
sp_UserOfflineSmall Offline
3
April 30, 2018 - 6:15 pm
sp_Permalink sp_Print sp_EditHistory

Hi Mynda

                        Good Morning

                      Thank you  for your  reply,  but,  I do not understand following  things

                     1)  I  don't understand  sumproduct formula  you have written.    =SUMPRODUCT((C6:C35*(B6:B35=E4)+(B6:B35=E5))), 
                          Please explain me.   Same  file which you gave me is attached again.  On workbook named Mitul's file for Amazon, eBay orders.xlsx 
                         there is  worksheet for all orders.  How can I clean leading spaces ?

   2)  Formula you gave me, it is for "Excel Book" , not "Excel" Pamphlet, and how can  we write "sumproduct" formula for both "Excel" Book
         & "Excel" Pamphlets, "Word" books  &  "Word" pamphlets,  "PowerPoint" books & "PowerPoint"  Video, "Outlook" books
                

                    3)  Data you gave me , is it Pivot Table ?  I don't think it is Pivot.Table because I don't think we can use sumproduct  in that?  Can We ? 
                         

                        Thank you very much.

                               Have a great day.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
May 1, 2018 - 9:34 am
sp_Permalink sp_Print

Hi Mitul,

1.1 You can learn SUMPRODUCT here

1.2 You can use TRIM to remove leading and trailing spaces: TRIM

2. The formula does include both books and pamphlets, as you can see it references cells E4 and E5 which contain the criteria. However, I omitted a set of parentheses, so the formula should be:

=SUMPRODUCT(C6:C35*((B6:B35=E4)+(B6:B35=E5)))

3. No, the formula is not in the PivotTable, it's outside the PivotTable under the headings you provided in the file.

Mynda

Avatar
MITUL PARIKH
Member
Members
Level 0
Forum Posts: 68
Member Since:
December 8, 2017
sp_UserOfflineSmall Offline
5
May 1, 2018 - 10:30 pm
sp_Permalink sp_Print sp_EditHistory

    Hi; Mynda;

             Good Morning

              This is Mitul

                Thank you  for your reply.

                   Still I don't understand  how formula include both books and pamphlets, calculated on  worksheet, both Excel Books & pamphlets  is $115.34,
                    how  did you get  $90.44 for both. Please explain me Sumproduct formula step by step. Printed sumproduct article, learned ,* reads "AND"
                     &  +  reads "OR".
              

                          Also,  "Sumproduct"  worksheet (from Excel_blog_workbook_2) is included with  worksheet of my data. Please explain me all formulas
                          on this worksheet and how did you get numbers that you received.      

                                                         
                         Will greatly appreciate it.

                  Sincerely;

                  Mitul
       

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 873
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
May 2, 2018 - 5:24 am
sp_Permalink sp_Print

Hello Mitul,

The formula should read

=SUMPRODUCT(C6:C36*(B6:B36=E4)+C6:C36*(B6:B36=E5))

And you need to check the data entered, below picture shows what text values that exists in column B.

Mitul-SumProduct.JPGImage Enlarger

 

Br,
Anders

sp_PlupAttachments Attachments
  • sp_PlupImage Mitul-SumProduct.JPG (17 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
May 2, 2018 - 3:54 pm
sp_Permalink sp_Print

Hi Mitul,

The original formula in the file was missing parentheses. If you replace it with the one I mentioned above i.e. this one:

=SUMPRODUCT(C6:C35*((B6:B35=E4)+(B6:B35=E5)))

It returns $115.34

In English it reads: sum the values in cells C6:C35 if the cells in cells B6:B35 contain the value in cell E4 OR the value in E5.

Anders is a variation of my formula, which also works.

Mynda
Avatar
MITUL PARIKH
Member
Members
Level 0
Forum Posts: 68
Member Since:
December 8, 2017
sp_UserOfflineSmall Offline
8
May 2, 2018 - 9:08 pm
sp_Permalink sp_Print sp_EditHistory

     Hi;  Mynda;

                             Same file is attached below . In  Cell E6 I  entered  formula you gave me =SUMPRODUCT(C6:C35*((B6:B35=E4)+(B6:B35=E5))), as
                                you'll  see , still returns $90.44 , not  $115.34.  Above  you told me :  "In English it reads: sum the values in cells C6:C35
                                 if  the cells in cells  B6:B35 contain the value in cell E4 OR the value in E5.  I like to  sum  the values  in cells C6 :C35
                                   for both values in cell  E4 AND in cell E5. 
 

                              Also,  I  entered  Anders formula in cell  F6  :  =SUMPRODUCT(C6:C36*(B6:B36=E4)+C6:C36*(B6:B36=E5)), it  still  gives $90.44,
                              not  $115.34 .

                              Please help me out.

                               Thank you very much.

                                  Have a great day.

        Sincerely;

              Mitul.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 873
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
9
May 3, 2018 - 1:12 am
sp_Permalink sp_Print

Hello Mitul,

I suggest you check the spelling in cell E5. You have typed in ”Excel” Pamphlets but in your data range you don’t have that text, you have ”Excel” Pamphlet. So remove the ending -s in cell E5.

So, please check your data. The formulas are ok.

Br,

/Anders

Avatar
MITUL PARIKH
Member
Members
Level 0
Forum Posts: 68
Member Since:
December 8, 2017
sp_UserOfflineSmall Offline
10
May 3, 2018 - 7:54 pm
sp_Permalink sp_Print sp_EditHistory

                Hello  Anders;

                   Good Morning
     

                        This is Mitul 

                     Thank you  for  your reply.
                                  

In  workbook attached ,   in Sumproduct formula total in Cell E7 is $204.13  because it  does not include amount in  Cell C7 even though I 
have written EXCEL" BOOK in E3  and included  in formula.  From Cells F2 :  F6 , total  should be  $165.28,  I included those cells in formula, still getting  $108.18.  Formula  in cell G7 should include $35.09 for PowerPoint Video, and total in  Cell H7 , total should be $28.93, not $46.06. Please explain me  what I did wrong in each sumproduct formula?   and  can you please give me correct answers ?  
                     
          Also on workbook named "additional getpivotdata practice" attached,  I entered exactly same formula in cell M9  on  Piv. Tab. -  group by 
          months  worksheet  as  sheet1 - cell J8, still  getting Mar -00. Please explain me  what I did wrong and correct my error.
                   
                    Thank you very much  for  both problems.
                                 Have a great day.             Sincerely;

                           Mitul.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 873
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
11
May 4, 2018 - 7:12 am
sp_Permalink sp_Print sp_EditHistory

Hello Mitul,

How are you? Sorry for a late response, I had some difficulties to get home from work today.

Questions regarding Document Copy-of-mitul_sumproduct_2.xlsx

Your first question is easy to answer, you have two spaces between the words in cell E4, remove one space between "Excel" and Book and you'll get your missing $90.44. The formula is correct.

Your second question regarding column F, it is once again an issue created due to data mismatch between what you reference too to what actually exists in column B. What has happened here is that for reference cells F2, F3 and F6 you look for "Word" Book in column B, there is one such post with the value $36.06, so you get that value three times. That is why you get the value 108.18 in cell F7.

The error lies in your data in column B.
In cell B10 "Word" Book is spelled with a space as first character.
In cell B11 "Word" Book is spelled with a space as first character plus two spaces between "Word" and Book.
In cell B18 "Word" Book is spelled correct, without a leading space and with one space between the words. This is the cell that matches the criterias in your formula.

To continue.
In cell B12 "Word" Pamphlet is spelled with nine spaces in the beginning, so if you add nine spaces in the beginning of your lookup reference in cell F4 you will get a match.
In cell B17 "Word" Pamphlet is spelled with eight spaces in the beginning and two spaces between the words, so if you add the same in your lookup reference in cell F5 you will get a match.

Your third question regarding column G, there is once again a typing error + a reference error.
In cell G5 you have a space as first character, remove that space.
Also, in cell G7 you have this formula --> =SUMPRODUCT($C$7:$C$36*(($B$7:$B$36=G4)+($B$7:$B$36=G5)))
Your data range goes to row 37, but you only look to row 36. Change 36 to 37 and you will get a match, if you have removed the leading space in cell G5 that is.

And lastly, your fourth question regarding column H.
It is once again a matter of data mismatch. In cells H4 and H6 the lookup reference "Outlook" Book is correctly spelled, but in column B you have only one such data post, that is in cell B15, so you get the value 23.03 twice.
In cell B16 you have two spaces between the words. So add an extra space between "Outlook" and Book in cell H6 and you will get correct result.
Also, you only look to row 36 in this formula too.

To summarize this Mitul, the formulas you have are correct, it is just that the lookup values don't match what you have in your Pivot Table, so you don't get the expected results due to that. You need to do a cleanup.

------------------------------------------

Questions regarding document ADDITIONAL-GETPIVOTDATA-PRACTICE.xlsx

In PIV. TAB. - GROUP BY MONTHS worksheet and in cell M9 the error lies in the number format set. You have a custom format which is set to MMM-YY, that is why you get mar-00. Change the number format to General and you will get the expected result of 66.24. Nothing wrong with the formula.

In Sheet1 worksheet you have a custom number format in cell J8 set to "$"# ##0,00. If you use the same custom number format in cell M9 for above "issue" you will get correct result also.

As you notice, the formulas are OK, it is just that everything else also need to be OK or else you will get errors.

I hope you get some help with this reply. Go and grab a cup of tea or coffee and struggle on. I have done the changes in the attached files, so you have some reference.

Br,
Anders

Avatar
MITUL PARIKH
Member
Members
Level 0
Forum Posts: 68
Member Since:
December 8, 2017
sp_UserOfflineSmall Offline
12
May 4, 2018 - 6:00 pm
sp_Permalink sp_Print sp_EditHistory

                Good Morning Anders; (my friend);

                     This is Mitul.

                      How are you ?            

              Yes,  You  gave me all correct answers  to my  concerns in  those 2 worksheets.   

         Thank  you very much  for your help.  I  truly appreciate it. 

           In workbook attached  , there is "SUMPRODUCT" worksheet, Please explain me each & every formula in each cell (especially K25) as to how
           we received numbers  which  are in each cell and why there is error named #VALUE! in cell K16 and what should be done in this cell not to
           have this error ? 
 
                        I  am aware in SUMPRODUCT formula  * reads "AND"  &   +  reads "OR"  

                             Thank you very much  for your help in advance.

      Sincerely;

                  Mitul.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 873
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
13
May 4, 2018 - 8:29 pm
sp_Permalink sp_Print

Hello Mitul,

Glad to help out, but this is more looking like a private class rather than just giving some help or advice on how to go on with a problem.

First: When checking your file there is no error in cell K16 when I open the file.

Second: What is your understanding of what is going on in the formula? I do think that you have got the understanding of SUMPRODUCT function, so how do you read the formula in cell K25?

=SUMPRODUCT((Volume)*((solarSystem="Rens")+(solarSystem="Endrulf"))*(jumps=6))/SUMPRODUCT(((solarSystem="Rens")+(solarSystem="Endrulf"))*(jumps=6))

One help if you want to see what is happening one step at a time is to click on cell K25, go to Formulas tab and run Evaluate Formula.

Br,

Anders

Avatar
MITUL PARIKH
Member
Members
Level 0
Forum Posts: 68
Member Since:
December 8, 2017
sp_UserOfflineSmall Offline
14
May 5, 2018 - 7:20 am
sp_Permalink sp_Print

          Hello  Anders;

                 This is Mitul.

                I am sorry IF you felt that you're giving me private class. 

               I will search  online for different SUMPRODUCT formula scenarios.

                Once again, I  am sorry.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1529
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
15
May 5, 2018 - 1:53 pm
sp_Permalink sp_Print sp_EditHistory

Hi Mitul,

Everyone who responds to a question on this forum is giving up their time to help out.

When you ask for Anders to 'explain me each & every formula in each cell' I think it shows that you would benefit from taking an Excel course and gaining a solid understanding of things like common Excel functions.

You'll find that a lot of people who are very good with Excel have taught themselves a great deal by reading blogs and forums and trying to work out solutions to problems themselves.

I'd encourage you to do the same and then when you get stuck please post in here and I'm sure you'll find people more than willing to help.

Phil

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 873
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
16
May 5, 2018 - 8:22 pm
sp_Permalink sp_Print

Hello Mitul,

As Philip writes, I do think you would benefit greatly of taking an Excel course because you will get good explanations of how the functions work, different techniques on how to use the functions for different scenarios and lots of training material to play with.

I am happy to help, that is partly why I am active in this forum. I also get training myself because some of the scenarios presented by different users I never tend to see and work with in my day-to-day work activities. I have for example never used SUMPRODUCT actively so I spent two hours to read and refresh my knowledge about it before answering your first question. While trying to help you I learned that SUMPRODUCT is a pretty good function.

I do hope you continue to struggle on and keep posting.

Br,
Anders

Avatar
MITUL PARIKH
Member
Members
Level 0
Forum Posts: 68
Member Since:
December 8, 2017
sp_UserOfflineSmall Offline
17
May 6, 2018 - 12:39 am
sp_Permalink sp_Print sp_EditHistory

         Hello Anders (my friend);

                     Good Morning

                      This is Mitul.

            Once again,  Thank you very much  for taking your time to give me correct answers  to my first SUMPRODUCT 4 problems I  faced.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Catalin Bombea, Bright Asamoah
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
David du Toit
Forum Stats:
Groups: 3
Forums: 24
Topics: 6358
Posts: 27805

 

Member Stats:
Guest Posters: 49
Members: 32335
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.