+ Reply to Thread
Results 1 to 2 of 2

SumIf formula or Sumif with pivottable combined question

  1. #1
    Registered User
    Join Date
    08-10-2015
    Location
    Netherlands
    MS-Off Ver
    Office 2013
    Posts
    18

    SumIf formula or Sumif with pivottable combined question

    Hi All,

    I will try to explain my question as good as possible.
    I have a pivottable from a data dump.
    This pivottable shows 2 columns
    - first is percentages
    - second is the number / count of those same percentages.
    So in my attached excel you will find an example.

    I am using a countif and a countifs formula to count the number of percentages
    in a certain range.

    <85%
    > 85% - < 120%
    >120%

    The problem I have is that some percentages are the same as you will see in the excel.
    My formula works :

    =AANTALLEN.ALS(A2:A8;">85%";A2:A8;"<120%") (Dutch version but I think you will automatically have the English formula when opening in your language right?)

    However I need a count if the % appears more than 1.
    Please see my excel with what I mean

    My pivottable shows:

    % Number of times
    78,50% 1
    45% 1
    123% 2
    90% 1
    34% 1
    98% 1
    140% 1

    Because I have my formula count in the first column I am having "gaps" for
    I actually need a formula that says look at the percentage and then if condition is met,
    take value from column 2
    How can I arrange that?

    Once again if you look at my excel file attached you will see what I mean.

    I really hope I have explained it well?
    I really would love to have the answer to this one.

    Thanks!
    Marco
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    08-10-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    7

    Re: SumIf formula or Sumif with pivottable combined question

    My first thought was to use a sumifs with an AND function in the criteria, but that seemed to fail. If someone knows how to make this work that'd be excellent, but for now I'll give you this:

    First I created another column (would be beside your pivot) and created this formula:

    =IF(A4<=0.85,1,IF(AND(A4>0.85,A4<1.2),2,IF(A4>=1.2,3,"")))

    Where column A is your %. This will label the categories as 1, 2, or 3. (<=85%, >85% & <120%, >=120%, respectively. Don't forget to use 'or equals to' in these types of formulas as appropriate!)

    Then I used this formula to do the sum:

    =SUMIFS(B4:B10,C4:C10,"=1")
    =SUMIFS(B4:B10,C4:C10,"=2")
    =SUMIFS(B4:B10,C4:C10,"=3")

    These three sum the "count" column of your pivot appropriately, based on the labels we assigned each row (1,2,3).

    See attachment for more details! Hope this helps!



    sumifs formule van pivottable.xlsx

+ 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] SUMIF combined with a multiplication
    By Deathwing in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-11-2015, 03:29 PM
  2. [SOLVED] Vlookup combined with SUMIF (Maybe)
    By dorian1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2015, 05:40 PM
  3. [SOLVED] SUMIF with combined rows
    By slacknoise in forum Excel General
    Replies: 1
    Last Post: 06-19-2014, 12:38 PM
  4. combined Sumif and Subtotal formula
    By CLoos in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2013, 07:39 PM
  5. Sumif & counta combined
    By kashaikh78 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-02-2013, 04:15 AM
  6. Can sumif and Max functions combined into one?
    By ReconMan in forum Excel General
    Replies: 4
    Last Post: 09-20-2011, 11:29 AM
  7. Replies: 1
    Last Post: 04-22-2005, 12:06 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