+ Reply to Thread
Results 1 to 6 of 6

Difference between Sumifs and Countifs in a chart

  1. #1
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Henderson, KY
    MS-Off Ver
    Excel 2007
    Posts
    152

    Difference between Sumifs and Countifs in a chart

    I have 2 formulas, 1 uses Sumifs and the other 1 uses Countifs.
    Is there a reason why the answer for the one with the Sumifs in it changes when I sort the data in the table, that those formulas use for data, yet the one that uses Countifs does not change?

  2. #2
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Henderson, KY
    MS-Off Ver
    Excel 2007
    Posts
    152

    Re: Difference between Sumifs and Countifs in a chart

    Here is an example workbook.
    The cells that change are AM5:BQ5
    The cells that stay the same are AM3:BQ3
    The data is sorted in Column E, if you "Select All", the answers in AM5:BQ5 change
    Attached Files Attached Files

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

    Re: Difference between Sumifs and Countifs in a chart

    Sumifs creates a sum of the numbers that meet the specified condition.

    Countifs counts how many cells meet the specified condition.

    If you have 20 number and 10 of them meet the specified condition, Countifs will return a 10. Sumifs will return the result of the sum of those 10 numbers. If the number values change, the Countifs still counts 10 valid numbers, but the Sumifs will come back with the current sum result.

  4. #4
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Henderson, KY
    MS-Off Ver
    Excel 2007
    Posts
    152

    Re: Difference between Sumifs and Countifs in a chart

    is there a way to write that formula, so that it doesnt change when you sort the table?

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

    Re: Difference between Sumifs and Countifs in a chart

    Sorting and filtering the table does not have any effect on the results of Countifs or Sumifs, because they reference the whole table.

    If you feel some of your results are wrong, inspect the formula with the Evaluate Formula tool to see what's going on.

  6. #6
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Henderson, KY
    MS-Off Ver
    Excel 2007
    Posts
    152

    Re: Difference between Sumifs and Countifs in a chart

    the results are right, but when I filter a column, the formula results change on the cell with the Sumifs in the formula.

+ 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