count values but exclude duplicates

[elynoy]

Hello, I need to count the number of times the letter C appears in a range of cells but i need to excelude the duplicate values out of that count.

Let me explain this better:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]day1[/TD]
[TD]day2[/TD]
[TD]day3[/TD]
[TD]day4[/TD]
[TD]day5[/TD]
[TD]day6[/TD]
[TD]day7[/TD]
[TD]day8[/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]16:00[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16:00[/TD]
[TD]24:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

in this table the letter "A" shows 2 times on diferent columns, so the person worked 2 days. The letter "B" shows 2 times but I need it to count as one time so it shows as the person worked different shifts in that day.

so, if I have this:

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD][/TD]
[TD]day1[/TD]
[TD]day2[/TD]
[TD]day3[/TD]
[TD]day4[/TD]
[TD]day5[/TD]
[TD]day6[/TD]
[TD]day7[/TD]
[TD]day8[/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]16:00[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16:00[/TD]
[TD]24:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need the person as letter "B" to show as working 2 days (count it as 2 times) and not count the letter 4 times.

This is based on a COUNTIF formula.

If there a way to do this with a simple formula or vba?


Best regards,
eLy

 

[elynoy]

30 possible shifts options. Starting M7 and finishing at AQ36. then depends on how many days the month have. 28,29,30 or 31 but the table is set on that M7 and AQ36 interval.

that was just an example sheet I'm trying so I dont mess up my original working sheet. when i get that to work I'll move it to the original sheet.

Maybe this will help you more. the original sheet:

https://prnt.sc/m2yj9j

and this is the sample file just in case:
http://s000.tinyupload.com/index.php?file_id=04593569787061828058

Best regards,
eLy

 

[Marcelo Branco]

An Example with 3 shifts

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td]

day1​

[/td][td]

day2​

[/td][td]

day3​

[/td][td]

day4​

[/td][td]

day5​

[/td][td]

day6​

[/td][td]

day7​

[/td][td]

day8​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

08:00​

[/td][td]

10:00​

[/td][td]

a​

[/td][td]

a​

[/td][td]

c​

[/td][td]

b​

[/td][td]

b​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

10:00​

[/td][td]

16:00​

[/td][td]

a​

[/td][td][/td][td]

c​

[/td][td]

b​

[/td][td]

b​

[/td][td][/td][td]

c​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

16:00​

[/td][td]

00:00​

[/td][td][/td][td][/td][td][/td][td]

b​

[/td][td][/td][td]

b​

[/td][td]

c​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Letter​

[/td][td]

Result​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

a​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

b​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

c​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in B7 copied down
Portuguese - BR
=SOMARPRODUTO(--(MATRIZ.MULT({1\1\1};--(C$2:J$4=A7))>0))

Note that the first array {1\1\1} has as many 1s as the number of shifts.

English
=SUMPRODUCT(--(MMULT({1,1,1},--(C$2:J$4=A7))>0))

M.

 

[elynoy]

Thank you, the problem was exactly the amount of \1 in there.

Seems to be working as expected.

best regards,
eLy

 

[Marcelo Branco]

You're welcome. Glad to help.

M.