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

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,483
Members
414,146
Latest member
marginmakerb

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
Top