count unique records with multiple conditions in 2 different sheets

[smuller74]

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

 

[Domenic]

Try the following formulas, which need to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF((Sheet1!A3:A1001="active")*(Sheet1!D3:D1001="X / X / X / X")*(Sheet1!E3:E1001="")*(Sheet1!G3:G1001="")*(Sheet1!H3:H1001="")*(Sheet1!C3:C1001<>""),MATCH(Sheet1!C3:C1001,Sheet1!C3:C1001,0)),ROW(Sheet1!C3:C1001)-ROW(Sheet1!C3)+1),1))

or

=SUM(IF(FREQUENCY(IF((Sheet1!A3:A1001="active")*(Sheet1!D3:D1001="X / X / X / X")*(Sheet1!E3:E1001&Sheet1!G3:G1001&Sheet1!H3:H1001="")*(Sheet1!C3:C1001<>""),MATCH(Sheet1!C3:C1001,Sheet1!C3:C1001,0)),ROW(Sheet1!C3:C1001)-ROW(Sheet1!C3)+1),1))

 

[smuller74]

YEEEEEAAAAHHH... it's working

Thank you very much Domenic !