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:

		day1	day2	day3	day4	day5	day6	day7	day8
08:00	16:00	a	a		b
16:00	24:00				b

<tbody>
</tbody>

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:

	day1	day2	day3	day4	day5	day6	day7	day8
08:00	16:00	a	a		b	b
16:00	24:00				b	b

<tbody>
</tbody>

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

 

[oldbrewer]

day1

day2

day3

day4

day5

day6

day7

day8

day1

day2

day3

day4

day5

day6

day7

day8

08:00

16:00

a

a

b

d

c

c

e

08:00

16:00

a

a

b

d

c

c

e

16:00

24:00:00

b

d

e

16:00

24:00:00

a helper table that blanks the lower of a pair of identical letters

then count the number of a,b,c,d,e

helper can be hidde

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col span="4"><col><col><col><col><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​			day1​	day2​	day3​	day4​	day5​	day6​	day7​	day8​
2​	08:00​	16:00​	a​	a​		b​	b​
3​	16:00​	00:00​				b​	b​
4​
5​	Letter​	Result​
6​	a​	2​
7​	b​	2​
8​

Formula in B6 copied down
=SUMPRODUCT(--(MMULT({1,1},--(C$2:J$3=A6))>0))

Hope this helps

M.

 

[elynoy]

Marcelo, can you add a sample? it seems to do exactly what I need but it gives me a #value error maybe because of language translation.

 

[Marcelo Branco]

Marcelo, can you add a sample? it seems to do exactly what I need but it gives me a #value error maybe because of language translation.

I don't understand your problem. What Excel version are you using?

M.

 

[elynoy]

I just noticed the value error is not translation. I searched but couldnt find any answer for it since it adds sumproduct and mmult.

I use excel 2010

Best regards,
eLy

 

[Marcelo Branco]

Excel 2010 US version?
Try to post here the formula you are using.

M.

 

[elynoy]

Excel 2010 Portuguese version

at the moment I'm using this formula but it works only if the letters are "separated"

=CONTAR.SE(F6:AI8;"A")

wich brings me to the problem of the same person working different shifts in the same day it will increas the countfor each shift.

best regards,
eLy

 

[elynoy]

None of them work. Can I add a sample for you to check?

eLy

 

[Marcelo Branco]

Excel 2010 Portuguese version

at the moment I'm using this formula but it works only if the letters are "separated"

=CONTAR.SE(F6:AI8;"A")

wich brings me to the problem of the same person working different shifts in the same day it will increas the countfor each shift.

best regards,
eLy

How many shifts? Two as in your original post or three (F6:AI8)?

M.