Help me fix a SUM formula please

[Jyggalag]

Hi all,

I have this formula:

=IFERROR(SUM(IF(FREQUENCY(IF($A$2:$A$101=A2;IF($D$2:$D$101=D2;IF($H$2:$H$101=H2;MATCH($I$2:$I$101;$I$2:$I$101;0))));ROW($A$2:$A$101)-ROW($A$2)+1);1));"0")

It checks the following sheet (please note that A2, A3 and A4 have the same value):
'

I want the formula to look at the value in A2. After this, it has to look up if A2 appears anywhere else in column A, and if the value of A2 does indeed appear anywhere else, it has to check if the respective values for this row in column D, H and I match the values in D2, H2 and I2. If they do, it should return the value "1", because they are the same item. However, if a single value in either D, H or I deviate, it should add it, so B2 returns "2", and "3" if it finds two more, and "4" if it finds 3 more unique values and so on.

I hope this makes sense? I have tried to do it above, but my formula is not working. It correctly returns "0" for row 5 and 6, as these have no values in columns D, H, and I.
It also correctly returns "2" for row 3 and 4, as the values in row 2 and 3 are similar.
However, it incorrectly returns "1" in cell B2, because clearly I4 is different from I3 and I2 and these rows have the same value in column A, so they are the same item, but with a different value in column I, which should then return the value "2".

I know this is very complex, but I would GREATLY appreciate if someone could fix my formula!

 

[Jyggalag]

what is your expectation with this:

mr excel questions 34.xlsm
	A	B	C	D
9	A	D	H	I
10	name1	X	Y	Z
11	name1	A	B	C
12	name1	X	Y	Z
13	name1	A	B	C
jyggalag

Since it is the same name in column A from row 10-13, the column where the formula is inserted (maybe column E10-E13 in your case, I use column B) should return the value "2" as there are a total of 2 different variations for "name1"

and for row 9 it should return "1" as there is only one variation of this, which is the combination of "A", "D", "H" and "I" as values

 

[awoohaw]

i was using row 9 to indicate the columns in your scenario. Sorry for the confusion.

please let me know what your expectations are in these scenarios:

mr excel questions 34.xlsm
	A	B		D				H	I	J
9		Expectation?
10	name1	1		X				Y	Z	same as 12
11	name1	1		A				B	C	same as 13
12	name1	2		X				Y	Z	same as 10
13	name1	2		A				B	C	same as 11
14	name1	3		Q				R	S	same A, diff rest
15	name1	4		J				K	I	same A diff rest
16
17	what if sorted this way:
18	name1	1		X				Y	Z	same as 23
19	name1	1		A				B	C	same as 20
20	name1	2		A				B	C	same as 19
21	name1	3		Q				R	S	same A, but the rest is different
22	name1	4		J				K	I	same A, but the rest is different
23	name1	2		X				Y	Z	same as 17
jyggalag

 

[Jyggalag]

The way it is sorted does not matter.

Since it is the same name in column A all the way through, column B should have the same results, as it is looking up "name1" in all the rows

and this result should be: 4, since there are a total of 4 different combinations for the values in column D-I, which are:
combination 1: "name1, X, Y, Z"
combination 2: "name1, A, B, C"
combination 3: "name1, Q, R, S"
combination 4: "name1, J, K, I"

Does this make sense? I know I am setting up a super confusing task

 

[awoohaw]

okay. so you want to count the unique combinations for A of D,H,I?

 

[Jyggalag]

The way it is sorted does not matter.

Since it is the same name in column A all the way through, column B should have the same results, as it is looking up "name1" in all the rows

and this result should be: 4, since there are a total of 4 different combinations for the values in column D-I, which are:
combination 1: "name1, X, Y, Z"
combination 2: "name1, A, B, C"
combination 3: "name1, Q, R, S"
combination 4: "name1, J, K, I"

Does this make sense? I know I am setting up a super confusing task

okay. so you want to count the unique combinations for A of D,H,I?

Yes exactly!

 

[awoohaw]

ok, then try this:

i'm sorry, my comment about I, it is text in I2,I3, number in I4.

mr excel questions 34.xlsm
	A	B		D				H	I	J
1	Identifier	number		whatever				final date	number
2	name1	3		25-01-2023				25-01-2024	1000000
3	name1	3		25-03-2023				25-01-2024	1000000	D3 <> D2
4	name1	3		25-03-2023				25-01-2024	50000	<< text in I
jyggalag

Cell Formulas
Range	Formula
B2:B4	B2	=COUNTA(CHOOSECOLS((UNIQUE(HSTACK($A$2:$A$4,$D$2:$D$4,$H$2:$H$4,$I$2:$I$4),FALSE)),1))

 

[Jyggalag]

ok, then try this:

i'm sorry, my comment about I, it is text in I2,I3, number in I4.

mr excel questions 34.xlsm
	A	B		D				H	I	J
1	Identifier	number		whatever				final date	number
2	name1	3		25-01-2023				25-01-2024	1000000
3	name1	3		25-03-2023				25-01-2024	1000000	D3 <> D2
4	name1	3		25-03-2023				25-01-2024	50000	<< text in I
jyggalag

Cell Formulas
Range	Formula
B2:B4	B2	=COUNTA(CHOOSECOLS((UNIQUE(HSTACK($A$2:$A$4,$D$2:$D$4,$H$2:$H$4,$I$2:$I$4),FALSE)),1))

it does not work unfortunately

I inserted it into B2 like this: =COUNTA(CHOOSECOLS((UNIQUE(HSTACK($A$2:$A$1001;$D$2:$D$1000;$H$2:$H$1000;$I$2:$I$1000);FALSE));1)) (took it down to row 1000)

and it returns "6", which makes no sense to me, as there are only 2 variations of "name1" in those rows

likewise, it should return "1" on row B5 and B6

 

[awoohaw]

it does not work unfortunately

View attachment 90960

I inserted it into B2 like this: =COUNTA(CHOOSECOLS((UNIQUE(HSTACK($A$2:$A$1001;$D$2:$D$1000;$H$2:$H$1000;$I$2:$I$1000);FALSE));1)) (took it down to row 1000)

and it returns "6", which makes no sense to me, as there are only 2 variations of "name1" in those rows

likewise, it should return "1" on row B5 and B6

i errored, i didnot filter for the name.
here it is updated in a LET function:

mr excel questions 34.xlsm
	A	B		D				H	I	J
1	Identifier	number		whatever				final date	number
2	name1	3		25-01-2023				25-01-2024	1000000
3	name1	3		25-03-2023				25-01-2024	1000000	D3 <> D2
4	name1	3		25-03-2023				25-01-2024	50000	<< text in I
jyggalag

Cell Formulas
Range	Formula
B2:B4	B2	=LET(name,$A2,names,$A$2:$A$4,colD,$D$2:$D$4,colH,$H$2:$H$4,colI,$I$2:$I$4, colstack,HSTACK(names,colD,colH,colI),filtcolstack, FILTER(colstack,names=name,""),uniqitems,UNIQUE(filtcolstack,FALSE),uniqnames,CHOOSECOLS(uniqitems,1),countuniqnames,COUNTA(uniqnames),countuniqnames)

 

[Jyggalag]

i errored, i didnot filter for the name.
here it is updated in a LET function:

mr excel questions 34.xlsm
	A	B		D				H	I	J
1	Identifier	number		whatever				final date	number
2	name1	3		25-01-2023				25-01-2024	1000000
3	name1	3		25-03-2023				25-01-2024	1000000	D3 <> D2
4	name1	3		25-03-2023				25-01-2024	50000	<< text in I
jyggalag

Cell Formulas
Range	Formula
B2:B4	B2	=LET(name,$A2,names,$A$2:$A$4,colD,$D$2:$D$4,colH,$H$2:$H$4,colI,$I$2:$I$4, colstack,HSTACK(names,colD,colH,colI),filtcolstack, FILTER(colstack,names=name,""),uniqitems,UNIQUE(filtcolstack,FALSE),uniqnames,CHOOSECOLS(uniqitems,1),countuniqnames,COUNTA(uniqnames),countuniqnames)

this formula is absolutely insane AND IT WORKS!!!

you are my savior awoohaw! that's a crazyyy formula I love it haha

thank you so much! you have no idea how much this helps me, really appreciate it <3

 

[awoohaw]

here it is with 2 diff names:

mr excel questions 34.xlsm
	A	B		D				H	I
1	Identifier	number		whatever				final date	number
2	name1	3		25-01-2023				25-01-2024	1000000
3	name1	3		25-03-2023				25-01-2024	1000000
4	name1	3		25-03-2023				25-01-2024	50000
5	name2	1		2023-02-04				2023-04-03	75000
6	name3	1		2023-02-04				2023-04-03	80000
7	name2	1		2023-02-04				2023-04-03	75000
jyggalag

Cell Formulas
Range	Formula