Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Modification of VBA from SUMIF to SUMPRODUCT

Posted on 2015-01-15
9
187 Views
Last Modified: 2015-01-15
Hello,

here is a nice piece of code written by Jon http://www.excelcampus.com/vba/convert-pivot-table-to-sumifs-formulas-vba-macro/

basically it converts the pivot table into SUMIFS formula.   while this works perfectly, i was wondering how this code could be modified to produce formulas of SUMPRODUCT instead of SUMIFS.

thanks.
0
Comment
Question by:Flora
  • 4
  • 4
9 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40551120
Are we supposed to go thru the article and find out how to download the code and then work on it to try to di-mistify this into SUMPRODUCT ???

Better if you post the code within a workbook that have data ... so we at least understand what it is all about and then we can look it up to see how to get you the SUMPRODUCT.

gowflow
0
 
LVL 6

Author Comment

by:Flora
ID: 40551157
thanks gowflow. i am always truly impressed with extra ordinary solutions that you provide in this forum.


i have attached two workbook.

DATAwithPIVOT.xlsm  this workbook, click on pivottable and then run the macro called Convert_Pivot_to_Formulas
then a new sheet will be automatically created and then you can see that macro generates array formula.

DATAwithPIVOT2.xlsm this workbook follow the same click on pivot table and then run macro Convert_Pivot_to_Formulas
will generate a new sheet with SUMIFs formula but it is not array formula.

these two different pivot table converted formula that gets generates with single macro, it could be far better that if SUMPRODUCT could be generated, to avoid any mistake of control+shift+enter, in case if anyone clicks on a cell and forgets to presss the cnt shft entr.

thank you very much gowflow.
DATAwithPIVOT.xlsm
DATAwithPIVOT2.xlsm
0
 
LVL 29

Accepted Solution

by:
gowflow earned 300 total points
ID: 40551214
Pls note that a solution with SUMPRODUCT will not eliminate 'control+shift+enter' as this means that the solution is an array solution. So if your intent is to go via SUMPRODUCT to avoid the 'control+shift+enter' then I think your on a wrong path !

gowflow
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 6

Author Comment

by:Flora
ID: 40551227
thanks gowflow,  at least if i can get SUMPRODUCT instead of SUMIFS that should be okay too.  even if it would still require the keystroke of controlshiftenter.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40551262
Why do you need SUMPRODUCT ?
0
 
LVL 6

Author Comment

by:Flora
ID: 40551361
becuase for users it is confusing having SUM(SUMIFS   instead of both, would it be possible to have one SUMPRODUCT?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40551926
Sorry but do not get it !!!

What kind of users do you have? is the result of the formulas correct ? does it give the correct values ??? If yes then what does it matter if the formula is 1 line or several or if it is SUM of SUM or ... ??

I do not understand.

gowflow
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 200 total points
ID: 40551933
Flora,  I find that SUMIFS is actually more intuitive than SUMPRODUCT and doesn't require any numerical conversion of logical results (ex. --(A2="x") ).  Also, the SUMIFS function processes faster than equivalent SUMPRODUCT function.  See this ExcelUser blog post that describes this.

Regards,
-Glenn
0
 
LVL 6

Author Comment

by:Flora
ID: 40552043
thank you gowflow and Glenn.

SUMIFS give correct result, so I will stick with that.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question