+ Reply to Thread
Results 1 to 8 of 8

Calculated Field as Count and Not Sum?

  1. #1
    Registered User
    Join Date
    01-28-2016
    Location
    florida
    MS-Off Ver
    2010
    Posts
    20

    Calculated Field as Count and Not Sum?

    Hello,

    Is it possible to have a calculated field in a pivot or any other way to obtain a "count" rather than a sum? I have a pivot that will update month over month and currently am having to update formulas each time because the row totals shifts.

    See attached example spreadsheet.
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Calculated Field as Count and Not Sum?

    You can do that with PowerQuery (free add-in for Ex2010 [edit]Pro Plus from MS site)

    see if it is what you want (2nd PT)

    Honestly, PT is not required to get final result
    Attached Files Attached Files
    Last edited by sandy666; 01-01-2018 at 11:33 PM.

  3. #3
    Registered User
    Join Date
    01-28-2016
    Location
    florida
    MS-Off Ver
    2010
    Posts
    20

    Re: Calculated Field as Count and Not Sum?

    Hello, Thank you for responding but I'm not sure what PT means.

    In the file you sent back the count is working for each line item but the CBO total is really what I'm looking for. Instead of it showing 5 for CBO1 Total, I'm wanting to see 2 to show that this CBO had 2 instances 1 in October and 1 in November. Whether it was 1 report for 10 doesn't matter. Is this possible?
    Last edited by AliGW; 01-02-2018 at 10:16 AM. Reason: Unnecessary quotation removed.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Calculated Field as Count and Not Sum?

    PT = Pivot Table

    Without the Power Pivot add-in, you cannot have a different calculation for the Grand Total column only.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Calculated Field as Count and Not Sum?

    you mean like this?
    Please Login or Register  to view this content.
    or attach excel file with manually entered data of result what you want to achieve (without PivotTable, will be easier)

  6. #6
    Registered User
    Join Date
    01-28-2016
    Location
    florida
    MS-Off Ver
    2010
    Posts
    20

    Re: Calculated Field as Count and Not Sum?

    See attached. Column F(Count) is what I'm hoping to see. I'm open to not using a pivot but I need to be able to provide a summary each month from the data on the details tab. How else can this be accomplished?

    Quote Originally Posted by sandy666 View Post
    you mean like this?
    Please Login or Register  to view this content.
    or attach excel file with manually entered data of result what you want to achieve (without PivotTable, will be easier)
    Attached Files Attached Files

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Calculated Field as Count and Not Sum?

    Oh, I see now .... I think you'll need PowerPivot as xlnitwit said in post #4 or easier will be with formula.
    Sorry but I am away from formula today

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,407

    Re: Calculated Field as Count and Not Sum?

    Here's a formula based on the raw data sheet that you might be able to adapt:

    =--(COUNTIFS($A$2:$A$42,"CBO1",$D$2:$D$42,"Oct-17")>0)+--(COUNTIFS($A$2:$A$42,"CBO1",$D$2:$D$42,"Nov-17")>0)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. [SOLVED] Count Columns in Pivot Table as Calculated Field
    By probablyjoel in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 08-20-2015, 03:16 PM
  2. Insert calculated field with count and sum elements
    By splreece in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-21-2015, 07:08 AM
  3. [SOLVED] Calculated Field for Percentage of a Count
    By trisoldee in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-22-2015, 07:55 PM
  4. [SOLVED] Pivot Table - Calculated Field - Count and Sum
    By rtabaldi in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-14-2014, 01:10 PM
  5. Replies: 0
    Last Post: 10-24-2013, 10:59 AM
  6. Count in a calculated field (Pivot Table)
    By Joćo Morsa in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-18-2013, 02:47 PM
  7. Replies: 0
    Last Post: 10-27-2010, 09:24 PM

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