Log In
Register
Home
December 8, 2017
Offline
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
July 16, 2010
Offline
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
December 8, 2017
Offline
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.
July 16, 2010
Offline
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
December 8, 2017
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
July 16, 2010
Offline
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
December 8, 2017
Offline
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.
VIP
Trusted Members
December 7, 2016
Offline
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
December 8, 2017
Offline
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.
VIP
Trusted Members
December 7, 2016
Offline
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
December 8, 2017
Offline
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.
VIP
Trusted Members
December 7, 2016
Offline
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
December 8, 2017
Offline
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.
October 5, 2010
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
1 Guest(s)
