COUNTIFS Counting Less or More Than it Should

[queenjauvier]

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

 

[Marcelo Branco]

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)))

Different operators (in red)

M.

 

[AhoyNC]

Look at this part of your formula.
Q23:Q5022,">595" is going to count values greater than 595 (596,597,etc.)

This part in your second formula is going to count values over 594.5 which would include 595 (595,596,597,etc)

 

[goeyj]

It looks like you are wanting to round up (values 5-9) and round down (values 1-4) for your nested arithmetic. See the following to set those values to the values in your integer example.

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

 

[goeyj]

Sorry, missed essential part of the ROUND function...

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