Pls Help! i need to count multiple dates if multiple criteria met

[lellolee]

Hi all,

My brain has given up, is there a way i can do this with formulas?
I'd have a sheet which contains data as below

[B]colour Date 1 Date 2[/B]
red 25 Nov 16 1 Dec 16
blue  1 Dec 16 2 Dec 16
blue  25 Nov 16 1 Dec 16
red 12 Dec 16 25 Nov 16

EG. if it is red and then i want to count how many 25 Nov 16 there are, then what formula should i use? ( i would like it to answer 2) for me, countifs seem to generate error each time

 

[xlstime]

try this formula with ctrl+shft+enter (array formula)

=SUM(IF($J$3:$J$6=K10,IF(K3:K6=$K$9,1,IF($L$3:$L$6=$K$9,1,0)),1))-1

let
k9 = 25nov
k10 = red

Hi all,

My brain has given up, is there a way i can do this with formulas?
I'd have a sheet which contains data as below

[B]colour Date 1 Date 2[/B]
red 25 Nov 16 1 Dec 16
blue  1 Dec 16 2 Dec 16
blue  25 Nov 16 1 Dec 16
red 12 Dec 16 25 Nov 16

EG. if it is red and then i want to count how many 25 Nov 16 there are, then what formula should i use? ( i would like it to answer 2) for me, countifs seem to generate error each time

 

[bosco_yip]

In G2 :

=COUNTIFS(A:A,E2,B:B,F2)+COUNTIFS(A:A,E2,C:C,F2)

or,

=SUMPRODUCT((A2:A5=E2)*(B2:C5=F2))

Regards