Unique Values Formula Based on Criteria

Try to concatenate multiple IFs for the conditions instead of using AND. For an example look here.

Originally Posted by zmster2033

Any idea as to why that method works as opposed to using an AND?

Because AND(...) returns a single result where you need an array of results.

Why are you using IFERROR?

Try this... New one..
=IFERROR(SUM(IF(FREQUENCY(IF(COUNTIFS($B64,$G$2:$G$59,$C$62,$E$2:$E$59),MATCH($A$2:$A$59,$A$2:$A$59,0)),ROW($A$2:$A$59)-1),1)),0)
It can be more better if provide a workbook..

The nested IF version is faster to calculate than the COUNTIFS version.

The bigger the range the bigger the difference.

Good deal. Thanks for the feedback!

Hi Tony...
You have said that Nested Ifs are always faster to use than countif version..
I request you to explain me the reason behind that..
Is there any article on that.. if yes, then please provide a link as well....

There is calculation timer code here:

http://msdn.microsoft.com/en-us/library/aa730921.aspx

The author of the code is a very highly respected expert on Excel efficiency.

I do this kind of testing quite often.

The best way to test is get an average of several timings. I usually get the average of 6 timings on increasingly larger ranges.

For example, time 100 rows, then 1,000 rows, then 10,000 rows, then 50,000 rows, etc., etc.

Hi Tony..
I read the article.. simply amazing..
but do agree with this paragraph in context of your "Nested ifs" statement..?

SUMIFS, COUNTIFS, and AVERAGEIFS
Excel 2007 has three new functions that you can use to SUM, COUNT, or AVERAGE using multiple criteria. In earlier versions of Excel, you had to use slow-calculating, hard-to-understand array formulas or SUMPRODUCT to use multiple criteria. The new functions are easy to use and fast to calculate.
SUMIFS(sum_range, criteria_range1, criteria1 [,criteria_range2, criteria2…])
COUNTIFS(criteria_range1, criteria1 [,criteria_range2, criteria2…])
AVERAGEIFS(average_range, criteria_range1, criteria1 [,criteria_range2, criteria2…])
These functions handle full column references ($A:$A) very efficiently by using special handling for the empty cells. The criteria that evaluates text cells can use the wildcard characters * (any set of characters) and ? (any single character). Because these functions are so much faster to calculate than equivalent array formulas, you should use them to replace your array formulas wherever possible.

I think you're missing the intended use of those functions.

He's saying:

Instead of using an array formula like this:

=SUM(IF(A1:A10="X",IF(B1:B10="Y",IF(C1:C10="Z",D1:D10))))

Use this normally entered formula:

=SUMIFS(D1:D10,A1:A10,"X",B1:B10,"Y",C1:C10,"Z")

For the application of counting unique entries with conditions the nested IF version is faster to calculate than the COUNTIFS version.

Use the timer code and see for yourself.

Okay. Tony..
Thanks for clarifying..
I will definitely try it..