# Unique Values Formula Based on Criteria

1. ## Unique Values Formula Based on Criteria

Good afternoon,

I have watched the following video and needing additional assistance:

I am attemping to count unique values based on 2 criteria. My issue is when adding an and within the inner most if statement my formula appears to not be working. Please advise what I am missing with this formula.

{=IFERROR(SUM(IF(FREQUENCY(IF(AND(\$G\$2:\$G\$59=\$B64,\$E\$2:\$E\$59=\$C\$62),MATCH(\$A\$2:\$A\$59,\$A\$2:\$A\$59,0)),ROW(\$A\$2:\$A\$59)-ROW(\$A\$2)+1),1)),0)}

2. ## Re: Unique Values Formula Based on Criteria

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

3. ## Re: Unique Values Formula Based on Criteria

Thank you for the response! That worked wonderfully. My formula has now been updated to the below. Any idea as to why that method works as opposed to using an AND?

{=IFERROR(SUM(IF(FREQUENCY(IF(\$G\$2:\$G\$59=\$B\$64,IF(\$E\$2:\$E\$59=C\$62,MATCH(\$A\$2:\$A\$59,\$A\$2:\$A\$59,0))),ROW(\$A\$2:\$A\$59)-ROW(\$A\$2)+1),1)),0)}

4. ## Re: Unique Values Formula Based on Criteria

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?

5. ## Re: Unique Values Formula Based on Criteria

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..

6. ## Re: Unique Values Formula Based on Criteria

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

The bigger the range the bigger the difference.

7. ## Re: Unique Values Formula Based on Criteria

Good call out. I have removed the IFERROR as it is redundant.

8. ## Re: Unique Values Formula Based on Criteria

Good deal. Thanks for the feedback!

9. ## Re: Unique Values Formula Based on Criteria

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....

10. ## Re: Unique Values Formula Based on Criteria

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.

11. ## Re: Unique Values Formula Based on Criteria

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.

12. ## Re: Unique Values Formula Based on Criteria

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.

13. ## Re: Unique Values Formula Based on Criteria

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1