+ Reply to Thread
Results 1 to 6 of 6

Countif,Pivot table & Pivot Chart Based on data

  1. #1
    Forum Contributor excelkeechak's Avatar
    Join Date
    07-21-2008
    Location
    India.
    MS-Off Ver
    2013 /2016
    Posts
    363

    Countif,Pivot table & Pivot Chart Based on data

    I m working on a database in which i need to Count the employees whose salary is based on Different ranges.I tried The Formula COUNTIF but i m not getting the accurate results.i dont know where i went wrong.I even Tried using the sumproduct or Sum (Array Formulas).

    I also need to Show the same in a pivot Table.For example all the employees ranging from 0 - 5000 ,5001 - 10000 and so on.

    How do i achieve this. Please tell me how to build pivot table & Pivot Chart as well as the regular countif formula.

    Thanks in advance.
    Attached Files Attached Files
    Last edited by excelkeechak; 02-27-2010 at 08:49 AM.
    THANKS
    ExcelKeechak

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Countif,Pivot table & Pivot Chart Based on data

    Hi excelkeechak,

    you can't use AND() in a SUMIF or COUNTIF. In Excel 2007 you can use SUMIFS and COUNTIFs with several parameters. In Excel 2003 you'll have to make do with sumproduct.

    for the first condition ( less than 5000), a simple Countif will suffice:

    =COUNTIF(A2:A120,"<5000")

    (The result is zero in your data sample, since there are no numbers smaller than 5000)

    For the next bandwidth, you'll have to use Sumproduct

    =SUMPRODUCT(--(A2:A120>5000),--(A2:A120<=10000))

    and so on.

    hth

  3. #3
    Forum Contributor excelkeechak's Avatar
    Join Date
    07-21-2008
    Location
    India.
    MS-Off Ver
    2013 /2016
    Posts
    363

    Re: Countif,Pivot table & Pivot Chart Based on data

    Thanks teylyn for that Formula.Also do tell me how do i show the same in a pivot table & Pivot Chart.
    I mean i want to show the Different Ranges of Salaries in a pivot Table.

  4. #4
    Forum Contributor excelkeechak's Avatar
    Join Date
    07-21-2008
    Location
    India.
    MS-Off Ver
    2013 /2016
    Posts
    363

    Re: Countif,Pivot table & Pivot Chart Based on data

    Is it possible to Display The same in the pivot Table or at least a chart based on the above data.
    Attached Files Attached Files

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Countif,Pivot table & Pivot Chart Based on data

    Hi,

    for a pivot table, you need to calculate the bandwidth in your data. See attached
    Attached Files Attached Files

  6. #6
    Forum Contributor excelkeechak's Avatar
    Join Date
    07-21-2008
    Location
    India.
    MS-Off Ver
    2013 /2016
    Posts
    363

    Re: Countif,Pivot table & Pivot Chart Based on data

    Thanks a ton teylyn.Now, i think i can create a dynamic pivot chart based on the pivot table designed by you. I had not used the lookup Function till Date. Thanks for the solution.

+ 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