Iterative COUNTIFS formula

[giovyledzep]

Hello everybody,

I am trying to get an iterative calculation of the number of times certain exact numbers appear in a range. I seem to be hitting a few bumps with the COUNTIF formula.

Example: If cells A1:A3 contain the numbers 1, 2 and 3 in any order i want the formula to return 1, so then i can make a cumulative sum through iterative calculation. Now, the problem is that I don't want the formula to return 1 if any of the cells in the range contain EITHER 1,2 OR 3, I want them ALL to contain EXACTLY 1,2 and 3, just in any order (it could be 2,1,3 or 3,1,2 or 2,3,1 etc..).

Any ideas?

Thank you very much

 

[Aladin Akyurek]

=(SUM(IF(FREQUENCY(A1:A3,A1:A3),1))=COUNT(A1:A3))+0

If the result obtains as desired, we get 1, otherwise 0.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?
=MAX(FREQUENCY(A1:A3,A1:A3))

If not, more details & varied examples please.

 

[giovyledzep]

Thank you for your reply!

I made the above example just to simplify the real task I'm trying to accomplish, but I'm better explain the situation in detail:
I have a range of three cells where if I press F9 i get 3 random colours (blue, green, yellow, red, brown, gold etc...) from a range located somewhere else in the sheet. I need a formula to return 1 every time the 3 colours I want come up in any order (let's say blue, green and yellow). So far, I've only tried formulas that return 1 any time there is EITHER blue, green OR yellow in any of the 3 cells in the range, but that's not what I'm looking for. I'm looking for a formula that returns 1 ONLY when the 3 entries are EXACTLY blue green AND yellow, in any order. I hope this clarifies.

Thank you

 

[Peter_SSs]

Edit: Sorry, my suggestion was incorrect. I'll re-think.

BTW, what version of Excel are you using?

 

[giovyledzep]

Thank you, unfortunately the formula returns 0. I was careful to confirm it with Ctrl+Shift+Enter too. I am using Excel 2016 for Mac.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=(SUM(IF(FREQUENCY(IF(1-(A1:A3=""),MATCH(A1:A3,A1:A3,0)),{1,2,3}),1))=3)+0

1 means all different, otherwise 0.

 

[giovyledzep]

Thank you very much, that worked!
Can you please write the formula for my example with the colours? Also I need another formula to return 1 anytime there are ONLY 2 of those colours (let's say blue and green) in that 3 cell range.

Example: blue, green, red returns 1; green, yellow, blue returns 1, red brown blue returns 0 (because there aren't BOTH blue and green)

Thank you

 

[Aladin Akyurek]

Thank you very much, that worked!
Can you please write the formula for my example with the colours? Also I need another formula to return 1 anytime there are ONLY 2 of those colours (let's say blue and green) in that 3 cell range.

Control+shift+enter:

=(SUM(IF(FREQUENCY(IF(1-(A1:A3=""),MATCH(A1:A3,A1:A3,0)),{1,2,3}),1))=2)+0

Is this what you mean?

Example: blue, green, red returns 1; green, yellow, blue returns 1, red brown blue returns 0 (because there aren't BOTH blue and green)

This sounds as restricting the count to a certain set of colors. If so, control+shift+enter:

=(SUM(IF(FREQUENCY(IF(1-(A1:A3=""),IF(ISNUMBER(MATCH(A1:A3,{"blue","green","red"},0)),MATCH(A1:A3,A1:A3,0))),{1,2,3}),1))=3)+0

This restricts the count evaluation only to the set of blue, green, and red.

 

[giovyledzep]

I meant this example:

I have a range of three cells where if I press F9 i get 3 random colours (blue, green, yellow, red, brown, gold etc...) from a range located somewhere else in the sheet. I need a formula to return 1 every time the 3 colours I want come up in any order (let's say blue, green and yellow). So far, I've only tried formulas that return 1 any time there is EITHER blue, green OR yellow in any of the 3 cells in the range, but that's not what I'm looking for. I'm looking for a formula that returns 1 ONLY when the 3 colours are EXACTLY blue green AND yellow, in any order.

Plus another formula that returns 1 when only TWO of those colours show up, and the 3rd can be any colour.
Example: blue, green, red returns 1; green, yellow, blue returns 1; red, brown, blue returns 0 (because there aren't BOTH blue and green)

Thanks