COUNTIFS Counting Less or More Than it Should

queenjauvier

New Member
Joined
Jan 2, 2018
Messages
3
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 :)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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)
 
Upvote 0
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))))
 
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top