Iterative COUNTIFS formula

giovyledzep

New Member
Joined
Jun 9, 2018
Messages
7
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.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=(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
Joined
May 28, 2005
Messages
49,531
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 9, 2018
Messages
7
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,531
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

BTW, what version of Excel are you using?
 
Last edited:

giovyledzep

New Member
Joined
Jun 9, 2018
Messages
7
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Jun 9, 2018
Messages
7
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Jun 9, 2018
Messages
7
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top