Amazing Use of COUNTIFS ...

Hi Folks..
This post is about.. speeding up array calculations..
The Use of countifs which I am gonna explain now is, I think, not known to much people (I suppose..)
If your Array construction has 4-5 criterias as in the attached example.. then you are now gonna love it..

Actually we usually use this type of construction in array formulas..

Formula:

=if((Range1=Criteria1)*(Range2=Criteria2)*(Range3=Criteria3),Row(RowRange))

In the attached file, I think, there are 5-6 such criterias, and It took me 8-12 steps (in Formula Evaluation) with a bit of hanging and excel crash, to get the answer..
Sounds bad naa...!

But My friends COUNTIFS and COUNTIF is here so you need not to worry...
If I use COUNTIFS, the above Equation will be look like this..

Formula:

=if(countifs(Criteria1,Range1,Criteria2,Range2,Criteria3,Range3),Row(RowRange))

yes, You got it right.. Criterias and Ranges shuffled their positions..

Now It will take only 2 step(in Formula Evaluation) to solve the above equation.. No Matter how many criterias are there..

what you are thinking is absolutely correct.. that is.. the array generated by (Range1=Criteria1)*(Range2=Criteria2)*(Range3=Criteria3) in first formula will always be equal to the array generated by countifs(Criteria1,Range1,Criteria2,Range2,Criteria3,Range3)..

So you can Imagine how fast excel gonna calculate it now..

you can also refer to the following link to have a detailed analysis of one of the easier examples..
http://excel-buzz.blogspot.in/2014/0...xsmall-as.html

As usual .. suggestion are always invited.. to speed up the calculations (whether array or non array.. (as our expert Mr XOR would like..))

don't hesitate .. and have a comment..

Vikas Gautam

one more clarification...
The formula is in the sheet2 column K.. in both files..
So try FORMULA AUDITING and enjoy the difference..

Both are array formulas...

Vikas Gautam

Thanks for Reputation XOR..
I think we all should endore this technique in our formulas..

Vikas Gautam

XOR I think we can use ">" or "<" in our criterias as well...

As in our array, criterias like... Range1>Criteria1 would become.. countifs(Criteria1,"<" & Range1)

Thanks for the great idea..

Vikas Gautam

yeah XOR...

Thanks for appreciation..

Vikas Gautam

I think as I have explained earlier..
To check the speed difference.. Formula Evaluation Steps can be a base..

Just count these steps for array(criterias).. and for countif criterias ....
and the %age difference in these counts.. should, I suppose, the %age difference of there performance...


Vikas Gautam

If you wanna add Multiple OR and AND conditions..
For OR Conditions...
use countif(Criteria1,Range1)+ Countifs(criteria2,Range2,Criteria3,Range3)
---------> Condition1 OR Condition2 AND Condition3

Vikas Gautam

Thanks.. sktneer..
that means a lot to me..

Vikas Gautam