Hey,
at first I wanna say that a lot of Threads here already helped me a lot ! Thanks to all !
Unfortunately I could not find a solution until now for the following array formula.
I need to count all unique records in Sheet 1 which matches more than 3 conditions in the same sheet but different columns. I need the result in Sheet 2.
The following array formula with 3 conditions works in my test sheet:
=SUM(IF(FREQUENCY(IF(Sheet1!A3:A1001="active",IF(Sheet1!D3:D1001="X / X / X / X",IF(Sheet1!E3:E1001="",IF(Sheet1!C3:C1001<>"",MATCH(Sheet1!C3:C1001,Sheet1!C3:C1001,0)))),ROW(Sheet1!C3:C1001)-ROW(Sheet1!C3)+1),1))
The array formula I theoretically need with 5 conditions is:
=SUM(IF(FREQUENCY(IF(Sheet1!A3:A1001="active",IF(Sheet1!D3:D1001="X / X / X / X",IF(Sheet1!E3:E1001="",IF(Sheet1!G3:G1001="",IF(Sheet1!H3:H1001="",IF(Sheet1!C3:C1001<>"",MATCH(Sheet1!C3:C1001,Sheet1!C3:C1001,0)))),ROW(Sheet1!C3:C1001)-ROW(Sheet1!C3)+1),1))
I guess that this combination of formulas can only handle 3 conditions because MATCH is highlighted after the error message.
But what other options do I have to get my result with an array formula ?
Every help is more then welcome...
Thanks in advance
smuller74
at first I wanna say that a lot of Threads here already helped me a lot ! Thanks to all !
Unfortunately I could not find a solution until now for the following array formula.
I need to count all unique records in Sheet 1 which matches more than 3 conditions in the same sheet but different columns. I need the result in Sheet 2.
The following array formula with 3 conditions works in my test sheet:
=SUM(IF(FREQUENCY(IF(Sheet1!A3:A1001="active",IF(Sheet1!D3:D1001="X / X / X / X",IF(Sheet1!E3:E1001="",IF(Sheet1!C3:C1001<>"",MATCH(Sheet1!C3:C1001,Sheet1!C3:C1001,0)))),ROW(Sheet1!C3:C1001)-ROW(Sheet1!C3)+1),1))
The array formula I theoretically need with 5 conditions is:
=SUM(IF(FREQUENCY(IF(Sheet1!A3:A1001="active",IF(Sheet1!D3:D1001="X / X / X / X",IF(Sheet1!E3:E1001="",IF(Sheet1!G3:G1001="",IF(Sheet1!H3:H1001="",IF(Sheet1!C3:C1001<>"",MATCH(Sheet1!C3:C1001,Sheet1!C3:C1001,0)))),ROW(Sheet1!C3:C1001)-ROW(Sheet1!C3)+1),1))
I guess that this combination of formulas can only handle 3 conditions because MATCH is highlighted after the error message.
But what other options do I have to get my result with an array formula ?
Every help is more then welcome...
Thanks in advance
smuller74