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]

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)

 

[Jyggalag]

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?

i'm not entirely sure what you mean, but english is my second language

so yes, let's say you start at B2 and you have "name1" in A2 and the value "1" in column D, "2" in H and "3" in I

It then has to look up these values for the remaining rows below and above it based on the name in A "name1". If it finds ANY OTHER cells in column A that contains cell value = "name1", it should look at their respective values in column D, H and I

If it does NOT find any differences in these rows and the ones in row 2, it means row 2 only has duplicates, or has no deviations, and it should return the result "1"

HOWEVER, if it finds a cell in A that is also named "name1", but it has another value in either column D, H or I, it should return "2" in cell B2, as "name1" now has some deviations. Likewise, if it finds two deviations, it should return "3" and so forth.

Does this make sense? Apologies for the long explanation

 

[awoohaw]

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