Iterative COUNTIFS formula

giovyledzep

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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
=(SUM(IF(FREQUENCY(A1:A3,A1:A3),1))=COUNT(A1:A3))+0

If the result obtains as desired, we get 1, otherwise 0.
 
Upvote 0
Welcome to the MrExcel board!

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

If not, more details & varied examples please.
 
Upvote 0
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
 
Upvote 0
Edit: Sorry, my suggestion was incorrect. I'll re-think.

BTW, what version of Excel are you using?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top