Results 1 to 11 of 11

Sum only 1's in Pivot table and ignore other value

Threaded View

  1. #1
    Registered User
    Join Date
    07-02-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 365
    Posts
    54

    Question Sum only 1's in Pivot table and ignore other value

    Dear All,
    I have dataset with different values. Two of the columns are having values from 1-18. The value 1 is important of all and i need its sum. What i need is to sum up all the 1's in pivot table.

    Earlier I have been using below formula in dataset table
    =IF(AND(A2=1,B2=1),SUM(A2+B2),IF(AND(A2=1,B2>1),1,IF(AND(A2>1,B2=1),1,0)))
    and then using this field sum in pivot table but due to some issues my field colleagues were messing in it and i dont want to include this in the table instead direct formula in pivot table.

    Now for summing up all the values I need some formula for calculated field.

    The data looks like below:
    SampleData.png

    The Answer that pivot should give me is sum up all 1s and give answer of 11.
    Last edited by naveeddil; 04-26-2019 at 09:45 PM. Reason: More clarification

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 11-09-2018, 03:09 AM
  2. pivot table to ignore data where n<2
    By Lourens in forum Excel General
    Replies: 2
    Last Post: 07-18-2011, 08:13 AM
  3. Get Pivot Table To Ignore Blanks
    By StevenAFC in forum Excel General
    Replies: 2
    Last Post: 09-11-2007, 05:06 AM
  4. How do I get a pivot table to ignore some values
    By Neostand in forum Excel General
    Replies: 3
    Last Post: 06-14-2006, 08:34 AM
  5. Get a pivot table to ignore data
    By Neostand in forum Excel General
    Replies: 1
    Last Post: 04-22-2006, 09:45 AM
  6. [SOLVED] Ignore 0 in pivot table
    By Johan in forum Excel General
    Replies: 0
    Last Post: 04-04-2005, 04:06 PM
  7. Pivot Table to Ignore N/As
    By Nelson in forum Excel General
    Replies: 2
    Last Post: 02-16-2005, 08:39 AM

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