+ Reply to Thread
Results 1 to 8 of 8

Write a formula to obtain a similar result to a pivot table summary

  1. #1
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Write a formula to obtain a similar result to a pivot table summary

    Hello,
    I am hoping to use a formula instead of a pivot table to get a summary result based on date in one column and summing the quantity from another column. The attached spreadsheet shows an example. I have widget quantities in column J and there are dates in column K. Projects will be added for the next few years, so there will be additional dates down the road.
    Attached Files Attached Files

  2. #2
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2308
    Posts
    1,472

    Re: Write a formula to obtain a similar result to a pivot table summary

    You could use a sumproduct.
    You first just need to make your row labels. I honestly just copied and pasted your pivot table values and used those.

    =SUMPRODUCT(((TEXT($K$2:$K$27,"mmm")=$F32)*(TEXT($K$2:$K$27,"yyyy")=$F$31))*$J$2:$J$27)

    "mmm" above is used if only looking for the month in 3 character acronym. change according to your row label set up.

    F31 above can be chamged to just "2020" or "2019" You can also just chamge the cell reference if you want that somewhere else.
    Attached Images Attached Images
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  3. #3
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Re: Write a formula to obtain a similar result to a pivot table summary

    Actually I should have done a better job of displaying the formatting I would like to use. I did not want to use a pivot table at all. What I would like to do is just use Columns X and Y (or any others) and write a formula in column Y based on the dates in column K and the quantities in column J. Hopefully this makes sense. I was not sure how to adapt what you provided into this. I am attaching an updated spreadsheet - note I have hidden some columns for neatness. Thank you
    Attached Files Attached Files
    Last edited by Hedy; 01-30-2020 at 05:22 PM. Reason: updated spreadsheet

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Write a formula to obtain a similar result to a pivot table summary

    Pl see file. x2:X13 formatted for mmm.
    In Y2 then copied across

    =IFERROR(1/(1/SUMPRODUCT((YEAR($K$2:$K$27)=Y$1)*(MONTH($K$2:$K$27)=MONTH($X2))*($J$2:$J$27))),"")
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 01-31-2020 at 12:41 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Re: Write a formula to obtain a similar result to a pivot table summary

    Thank you, but is there a formula that will calculate the quantities by month and year in the format below. I have attached an updated spreadsheet.

    Column X Column Y
    Feb-19
    Mar-19
    Apr-19
    May-19
    Jun-19
    Jul-19
    Aug-19
    Sep-19
    Oct-19
    Nov-19
    Dec-19
    Jan-20
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Write a formula to obtain a similar result to a pivot table summary

    Pl see file. x15:X48 formatted for Mmm-yyyy.
    In Y15 then copied down.

    =IFERROR(1/(1/SUMPRODUCT((YEAR($K$2:$K$27)=YEAR($X15))*(MONTH($K$2:$K$27)=MONTH($X15))*($J$2:$J$27))),"")
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Re: Write a formula to obtain a similar result to a pivot table summary

    This is perfect - thank you so much!

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Write a formula to obtain a similar result to a pivot table summary

    Thanks for feedback and rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 05-11-2018, 08:54 PM
  2. [SOLVED] Sumifs formula not showing correct result, one of criteria is from pivot table
    By Ishwarind in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2017, 07:59 AM
  3. [SOLVED] how to write row formula numbering in calculated field in pivot table
    By JEAN1972 in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 09-05-2017, 11:00 AM
  4. [SOLVED] Extract 9 largest categories - similar to pivot table but need formula
    By Thanks4helping in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-19-2016, 04:32 PM
  5. Formula result to drive Pivot Table Field change
    By Mgiles in forum Excel General
    Replies: 1
    Last Post: 12-21-2011, 08:57 PM
  6. Pivot Table Filter by Formula Result
    By WAW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2010, 06:45 AM
  7. Replies: 2
    Last Post: 04-19-2007, 08:46 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1