# Iterative COUNTIFS formula

#### giovyledzep

##### New Member
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

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

##### MrExcel MVP
=(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

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

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

If not, more details & varied examples please.

#### giovyledzep

##### New Member

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

##### MrExcel MVP, Moderator

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

BTW, what version of Excel are you using?

Last edited:

#### giovyledzep

##### New Member
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.

##### MrExcel MVP

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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

Replies
8
Views
141
Replies
21
Views
273
Replies
0
Views
155
Replies
3
Views
240
Replies
9
Views
95

1,137,155
Messages
5,679,923
Members
419,862
Latest member
Bluewings666

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back