+ 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
    15

    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
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    BE
    MS-Off Ver
    PC: 97 / 2016 (PQ/PP)
    Posts
    5,793

    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.
    sandy (NO CAPS)
    Excel can do everything but still doesn't read user's wishful thinking
    The ready made solution will not teach anyone anything

    A logical description of the problem is the basis of dialogue and a rapid solution. Nobody sits in your head to understand your way of thinking
    Note: English is not my native language
    Keep It Sophisticatedly Simple

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

    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 Expert xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    5,458

    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
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    BE
    MS-Off Ver
    PC: 97 / 2016 (PQ/PP)
    Posts
    5,793

    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
    15

    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
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    BE
    MS-Off Ver
    PC: 97 / 2016 (PQ/PP)
    Posts
    5,793

    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
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)
    Posts
    13,320

    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!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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