Formula to count non duplicated numbers with relative columns criteria

[Valderio]

Hello all,

I ll try to be concise;

I have 2 sheets; one sheet have a table named BDc; this table have 3 fields [Nº], [Data da consulta], [Genótipo]; [Nº] have ID numbers of patients (there are duplicated ones); [Data da consulta] have dates of apointments; [Genótipo] have genotipic classes like "1a", "1b", "2a", etc...

In the other sheet i have report with some data, like how many patients come between some dates, etc. (start date of range is in B1 cell and end date in C1).

So what i want is a formula that i can count non duplicate patients ID, that come between dates range, with genotipic 1 (so count 1a + 1b), 2, 3, 4, etc

I start do this, but it doesn't work
=SUMPRODUCT(((BDc[Data da consulta]>=B1)*(BDc[Data da consulta]<=C1)*(BDc[Genótipo]="*1*"))/(COUNTIFS(BDc[Nº];BDc[Nº]; BDc[Data da consulta];">="&B1;BDc[Data da consulta];"<="&C1)+((BDc[Data da consulta]<=B1)+(BDc[Data da consulta]>=C1))))

I think is because "*1*", cause if i use "1a", the result is correct, but i really need the contain "**" and not a specific value

Does anyone can help??

Thx

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF((BDc[Nº]<>"")*(BDc[Data da consulta]>=B1)*(BDc[Data da consulta]<=C1)*ISNUMBER(SEARCH(1;BDc[Genótipo]));MATCH(BDc[Nº];BDc[Nº];0));ROW(BDc[Nº])-ROW(INDEX(BDc[Nº];1;1))+1);1))

 

[Valderio]

Thx Aladin it works just perfect I don't master the FREQUENCY formula yet!

Thx for your quick answer