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]

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.

Please try to test what you are given...


Control+shift+enter, not just enter:


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

ThreeItemColorList is a range housing the 3 colors of interest. For example:

Book1
	J
1	green
2	red
3	blue
Sheet2

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)…

You are repeating exactly what you said in the previous post. I'll repeat my reply too for I don't know whether it fails to meet your specification or not.

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

Example:

Book1
	K
1	blue
2	green
Sheet2

 

[Peter_SSs]

I am using Excel 2016 for Mac.

I don't have a Mac to test but you could try this for the original problem where
- the random 3 colours are in A1:A3
- the 3 colours that you are trying to check for are in E1:E3

Confirmed with C+S+E
{=--(CONCAT(AGGREGATE(15,4,IFERROR(MATCH(A1:A3,E1:E3,0),0),{1,2,3}))="123")}



One thing that isn't clear about your arrangement: When you generate the 3 random colours in A1:A3, is it possible that a particular colour can occur twice or three times? That is, could A1:A3 be as follows?
blue
red
blue

 

[giovyledzep]

Thanks, unfortunately neither formula works here and they both return 0.

 

[giovyledzep]

Thank you, I tried the formula but it only returns 0.

To clarify, each colour has a unique number assigned when being generated, so it is not possible for one colour to appear twice or more.

Thanks

 

[Aladin Akyurek]

Thanks, unfortunately neither formula works here and they both return 0.

Thank you, I tried the formula but it only returns 0.

To clarify, each colour has a unique number assigned when being generated, so it is not possible for one colour to appear twice or more.

Thanks

Which formula are you addressing?

 

[jtakw]

Hi,

Cross posted here: https://www.excelforum.com/excel-fo...6-iterative-countifs-formula.html#post4914591

 

[Peter_SSs]

.. it is not possible for one colour to appear twice or more.

In that case a much simpler formula is available. Here it is working with both a success in matching the 3 colours and a failure.

Excel Workbook
	A	B	C	D	E
1	yellow		1		blue
2	green				green
3	blue				yellow
Three colours 1

Excel Workbook
	A	B	C	D	E
1	red		0		blue
2	green				green
3	blue				yellow
Three colours 2

Thank you, I tried the formula but it only returns 0.

If you are still getting 0 when you should be getting a 1 then two possible reasons are

a) You are not entering the formula as an array formula correctly. On a Mac for Excel 2016 it may be Cmd+Shift+Return possibly? (I'm not sure). Do you see the {} surrounding the formula in the formula bar after entry of the formula and selecting the cell, & remembering that the are {} are not manually typed.

b) One or more of the colours somewhere has leading or trailing space(s) or some other non-visible character.