Formula for Multiple criteria

[ysarchana]

can you please suggest a formula?

Example: For division x, ranging from A2:A100, please return count <=0.08,ranging from B2:B100

For the same division x, please return count >0.08 but <0.2 etc

It is more like applying filters,but with formula, like filtering on divisions and counting no of emps below 1 month experience, no of emps > 1 month but less than 3 months so on.

Please help..Many thanks

 

[Luke M]

I'm not quite sure on your layout, but it sounds like you can do something like this:

For a single boundary:

=COUNTIF(B2:B100,"<=0.08")


For a double boundary:

=COUNTIF(B2:B100,"<0.2")-COUNTIF(B2:B100,"<=0.08")


Also, if you have XL 2007+, you might want to read about COUNTIFS and SUMIFS in the XL help file.

 

[ysarchana]

Hi Luke,

Thanks for your quick reply. I am referring to multiple criteria, let me try to explain:

In the division column, please filter on finance, and in finance, please return a count if length of service is less than 1 month, for the same division, please return count of length of service greater than 1 month but less than or equal to 2 months and so on. As I mentioned,its more like applying filters but using a formula. If we have filters on,then we first filter on Division-Finance, and then filter on Length of service, and then count matching our criteria. But instead is there a better way than manually counting them like this..


Many thanks

 

[Luke M]

Ah! For that type of multiple criteria, I think your best bet will be to use SUMPRODUCT. Chandoo does a good explanation here:

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/


In the end, you should have something like:

=SUMPRODUCT((DivisiionCol="Finance")*(ServiceCol>=30)*(ServiceCol<=60))