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]

Welcome to MrExcel...

Assuming your data sits in range A1:E22, try:

=SUM(IF(FREQUENCY(IF(A2:A22<>"",IF(ISNUMBER(MATCH(C2:C22,{"a","c"},0)),IF(D2:D22>0,MATCH(A2:A22,A2:A22,0)))),ROW(A2:A22)-ROW(A2)+1)>0,1))

Array entered, i.e. with CTRL+SHIFT+ENTER.

The formula uses column D as this is where data for 24/10/2015 exists. It is possible to select multiple columns and add additional functionality into the formula to lookup the column of interest, but I'm unsure whether this is a concern or not for you.

Hope it helps.

Matty

 

[gaz_chops]

Maybe

=COUNT(IF(C2:C22="C",IF(D2:D22>0,1)))

Enter as an array

 

[admiral100]

Hi,

why 5 ?
I count only 3 persons that made the task "a" and "c" on the 24-10-2015 : Anne , Joe, Magdalena

 

[Matty]

Hi,

why 5 ?
I count only 3 persons that made the task "a" and "c" on the 24-10-2015 : Anne , Joe, Magdalena

5 is correct (Anne, Joe, Magdalena, Anthony and Mary). The formula I provided returns 5.

Matty

 

[admiral100]

I think caeiro01 ask "a" AND "c" not "a" OR "c" .....

 

[caeiro01]

Welcome to MrExcel...

Assuming your data sits in range A1:E22, try:

=SUM(IF(FREQUENCY(IF(A2:A22<>"",IF(ISNUMBER(MATCH(C2:C22,{"a","c"},0)),IF(D2:D22>0,MATCH(A2:A22,A2:A22,0)))),ROW(A2:A22)-ROW(A2)+1)>0,1))

Array entered, i.e. with CTRL+SHIFT+ENTER.

The formula uses column D as this is where data for 24/10/2015 exists. It is possible to select multiple columns and add additional functionality into the formula to lookup the column of interest, but I'm unsure whether this is a concern or not for you.

Hope it helps.

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

 

[gaz_chops]

Try

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

 

[caeiro01]

Try

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

Hi gaz_chops

in the offset, why did you chose C2?

i'm having some erros in some queries. is there any other option?

tks

 

[gaz_chops]

C2 is the start point for the offset, match will return 1 for the 24/10/15 or 2 for the 25/10/15, it will then reference either D2 or E2 (1 or 2 columns from C2), the 21 tells it to use 21 rows.

What are your data ranges? Post the actual formula you are using.