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

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.

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

    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
       

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.

Br,
Anders

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

     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.

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

                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.

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

                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.

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

          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.

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