Using Countif to count only Capital Leter (i.e. D) and lower case (i.e. d)

[neetal]

I am trying to set up a worksheet that shows shifts using a Capital "D" and a lower case "d" (one is for a 12 hour shift, the other for only 6 hours). Is there any way to set up a Countif formula that makes the distinction? Thank you in advance for your consideration.

[Jonmo1]

Unfortunately, countif is not case sensitive.

You could use sumproduct with the exact function..

=SUMPRODUCT(--(EXACT(A1:A100,"D")))
and
=SUMPRODUCT(--(EXACT(A1:A100,"d")))

[Tony Valko]

Try this...

Data Range

	A	B	C	D
1	-----	-----	-----	-----
2	D		D	5
3	D		d	4
4	d
5	d
6	d
7	D
8	d
9	D
10	D

This formula entered in D2 and copied down:

=SUMPRODUCT(--(EXACT(A$2:A$10,C2)))

[:) Sixthsense :)]

Use Exact() with Sumproduct() combination


In A1 to A5

d
D
d
D
D

=SUMPRODUCT(--EXACT(A1:A5,"d"))

Or

=SUMPRODUCT(--EXACT(A1:A5,"D"))

[Vikas_Gautam]

hey bro,

use this array formula....

{=Count(if(A1:A10="D",Row(A1:A10)))}

for counting "d"
just replace it with "d"

For this formula you have to write..
=Count(if(A1:A10="D",Row(A1:A10)))

Then press control+ shift+ enter...

For any explanation.. just make a post..


If the suggestion helps you, then Click *below to Add Reputation

[Tony Valko]

hey bro,

use this array formula....

{=Count(if(A1:A10="D",Row(A1:A10)))}

for counting "d"
just replace it with "d"

That won't work. The IF function doesn't differentiate text case. In the IF function D and d are the same.

[:) Sixthsense :)]

use this array formula....

{=Count(if(A1:A10="D",Row(A1:A10)))}

Logical Part of If is not Case Sensitive, so it won't work

[neetal]

Absolutely perfect! Thank you SO much! Never occurred to me that I could use SUMPRODUCT instead of COUNTIF. Thank you SO much for a perfect reply in such a speedy timeframe. You Rock!!!

[Jonmo1]

You're welcome.

[neetal]

Absolutely perfect! Thank you SO much! Never occurred to me that I could use SUMPRODUCT instead of COUNTIF. Thank you SO much for a perfect reply in such a speedy timeframe. You Rock!!!

[Tony Valko]

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[XOR LX]

A fight between SUMPRODUCT and COUNTIF would be a tough one to call...

[neetal]

Thank you SO much for letting me know about the "solved" tool. As you can no doubt tell, I'm a newbie to this forum. Again, thank you so much for all your help!