+ Reply to Thread
Results 1 to 2 of 2

COUNTIFS Function Counting More (or Less) than it Should

  1. #1
    Registered User
    Join Date
    01-18-2018
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    1

    COUNTIFS Function Counting More (or Less) than it Should

    I am having some trouble with my COUNTIFS function counting more numbers than it should (I think). For both functions, they're counting form the exact same pool of 5000 generated numbers. Here's the function with just the integers in it:

    =SUM(COUNTIFS(Q23:Q5022,">595",R23:R5022,">3", S23:S5022, "<55"),COUNTIFS(Q23:Q5022,">595",R23:R5022,">3",S23:S5022,">55"))

    And here's the function with some nested arithmetic:

    =SUM(COUNTIFS(Q23:Q5022,">"&AC17+(AC18*AL25),R23:R5022,">"&AD17+(AD18*AL25),S23:S5022,"<"&AE17+(AE18*AL25)),COUNTIFS(Q23:Q5022,">"&AC17+(AC18*AL25),R23:R5022,">"&AD17+(AD18*AL25),S23:S5022,">"&AE17+(AE18*AL25)))

    For reference: AC17 + (AC18*AL25) = 602.4 + (14.3492*(-0.55)) = 594.507

    AD17 + (AD18*AL25) = 4.8 + (2.7203*(-0.55)) = 3.304

    AE17 + (AE18*AL25) = 59 + (7.4027*(-0.55)) = 54.929

    What should happen is that the two functions should compute the same value since they have the exact same criteria, ranges, and conditional operators. However, the value that I get from the first function is 3401, whereas the value outputted by the second function is 3464. Is this caused by an issue of rounding in excel? Am I forgetting some important syntax component in the second function?

    Thanks for the help

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: COUNTIFS Function Counting More (or Less) than it Should

    Well there are two comments I'm going to make offhand.

    1) They are NOT the exact same criteria, even if the ranges and conditional operators are the same. Formula 1 is comparing to integers; Formula 2 is comparing to non-integers. Licking my thumb and sticking it in the wind, I expect that's where the differences are from.

    2) Especially since, functionally, you're counting for everything NOT equal to 55 (or 54.929).
    Please Login or Register  to view this content.
    Should deliver the exact same result as formula 1.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

+ 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] COUNTIFS formula Not counting
    By rz6657 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-21-2017, 12:32 PM
  2. [SOLVED] COUNTIFS - Counting in order in one column, Counting only specific cells In another
    By kslattery in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2017, 01:17 PM
  3. How can I run CountIfs + CountIfs without double counting?
    By hiitsjessie in forum Excel General
    Replies: 1
    Last Post: 02-06-2017, 04:49 PM
  4. COUNTIFS not counting all cells
    By Doppelganger in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-05-2015, 04:53 PM
  5. [SOLVED] COUNTIFS is double counting
    By infiniticihr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-22-2014, 01:09 PM
  6. Countifs and Double Counting
    By jlacsina in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2013, 09:55 PM
  7. Countifs not counting, returns #value
    By Qualo_Jinn in forum Excel General
    Replies: 3
    Last Post: 12-09-2011, 04:13 PM

Tags for this Thread

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