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!

 

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

 

[awoohaw]

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

My pleasure. I am happy you were able to find a solution in the forum.

Best Wishes!

 

[awoohaw]

can you sanitize your data and post as a xl2bb mini worksheet (link below)?
in worst case sanitize the data and post as a table.

Also, provide some expected results of the formula.

Thanks in advance.

 

[Jyggalag]

Identifier	number	Period	whatever	start date	y	x	final date	number
name1	1	59,00​	25-01-2023​	25-03-2023​			25-01-2024​	1000000​
name1	2	61,00​	25-03-2023​	25-05-2023​			25-01-2024​	1000000​
name1	2	61,00​	25-03-2023​	25-05-2023​			25-01-2024​	500000 can you sanitize your data and post as a xl2bb mini worksheet (link below)? in worst case sanitize the data and post as a table. Also, provide some expected results of the formula. Thanks in advance.

 

[Jyggalag]

can you sanitize your data and post as a xl2bb mini worksheet (link below)?
in worst case sanitize the data and post as a table.

Also, provide some expected results of the formula.

Thanks in advance.

Expected results: 2 in B2, 2 in B3, 2 in B4, and 0 in B5 and B6

 

[Jyggalag]

UPDATE

I just noticed that my value in D2 is different from D3 and D4 omg embarrassing ! sorry haha

however, the formula does not correctly check column I and the values in column I, please update it somebody so it also does this (same as with D and H)

 

[Jyggalag]

UPDATE

I just noticed that my value in D2 is different from D3 and D4 omg embarrassing ! sorry haha

however, the formula does not correctly check column I and the values in column I, please update it somebody so it also does this (same as with D and H)

NEW UPDATE

okay my formula apparently does not work anyway

can someplace update it? I want the formula to be pasted into column B's cells and then it has to lookup the name of the respective cell in column A. If this appears anywhere else in column A, it has to see: Do these A cells have the same row values for column E, H and I? If yes, they are duplicates, and the total amount in the cell in B should be "1". However, if any values in either E, H and I deviate, it should +1 these instances and return "2" if it finds one that deviates, "3" if it finds 2 that deviates and so on...

is this possible? very complex formula I know @Fluff maybe?

 

[Jyggalag]

NEW UPDATE

okay my formula apparently does not work anyway

can someplace update it? I want the formula to be pasted into column B's cells and then it has to lookup the name of the respective cell in column A. If this appears anywhere else in column A, it has to see: Do these A cells have the same row values for column E, H and I? If yes, they are duplicates, and the total amount in the cell in B should be "1". However, if any values in either E, H and I deviate, it should +1 these instances and return "2" if it finds one that deviates, "3" if it finds 2 that deviates and so on...

is this possible? very complex formula I know @Fluff maybe?

May need a completely new formula btw, so please do not take inspiration from the one I posted, as it apparently compares the values in the columns to that of column I, which is a great misunderstanding.....

 

[awoohaw]

so, to summarize, you want all records where A,D,H,I all match to make the value of the cell 1.
In all other cases where A matches to increment by 1.
What happens if a secondary case matches all A,D,H,I?
2 records with (A,D2,H2,I2) vs 2 records with (A,D1,H1,I1)