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

    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
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    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
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

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

    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,080

    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
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    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
    19

    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
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    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) & 365 Subscription (Win 10 - Home)
    Posts
    19,807

    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!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    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