Count unique values that match 2 or more criteria

[caeiro01]

Hi all,

i need count the number of persons assign to some tasks in one particular day.
Ex. how many persons made the task "a" and "c" on the 24-10-2015?

the answer should be 5

tks
Antonio

name	Status	process	24-10-2015	25-10-2015
Anne	Actual	a	1	1
Joe	Actual	a	1	3
Mary	Actual	a
Richard	Actual	a
Joe	Actual	a
Magdalena	Actual	a	1
Anthony	Actual	a	1	2
Anne	Actual	b	1	1
Joe	Actual	b		3
Anne	Actual	b
Mary	Actual	b	2	1
John	Actual	b	1
Magdalena	Actual	b	3	1
Anthony	Actual	b	1	2
Anne	Actual	c	3	3
Joe	Actual	c	2
Anne	Actual	c	1
Mary	Actual	c	3
John	Actual	c		1
Magdalena	Actual	c	2	2
Anthony	Actual	c		2

<tbody>
</tbody>

 

[Matty]

Hi Matty,

tks, this is brilliant.
However, i'm only missing one thing. I need the formula to select the date column that is mentioned in cell H1 (in this case, is 24/10/2015, but if would be 25/10/2015, the formula shoud search in E2:E22)

tks for the help
Antonio

Try:

=SUM(IF(FREQUENCY(IF(A2:A22<>"",IF(ISNUMBER(MATCH(C2:C22,{"a","c"},0)),IF(INDEX(D2:E22,,MATCH("24/10/2015"+0,D1:E1,0))>0,MATCH(A2:A22,A2:A22,0)))),ROW(A2:A22)-ROW(A2)+1)>0,1))

Submitted with CTRL+SHIFT+ENTER.

To be honest, we'd be much better using Worksheet cells rather than hard coding values into the formula itself. An example of this would be:

=SUM(IF(FREQUENCY(IF(A2:A22<>"",IF(ISNUMBER(MATCH(C2:C22,[COLOR=#ff0000]G2:G3[/COLOR],0)),IF(INDEX(D2:E22,,MATCH([COLOR=#ff0000]H2[/COLOR],D1:E1,0))>0,MATCH(A2:A22,A2:A22,0)))),ROW(A2:A22)-ROW(A2)+1)>0,1))

Where G2:G3 contains the Statuses of interest and H2 contains the Date of interest. Again, the formula needs submitting with CTRL+SHIFT+ENTER.

Matty

 

[gaz_chops]

Or a shorter version using offset.......

=SUM(IF(FREQUENCY(IF(C2:C22={"a","c"},IF(OFFSET(C2,,MATCH(H1,D1:E1,0),21,)>0,MATCH(A2:A22,A2:A22,0))),ROW(A1:A21))>0,1))