case sensitive comparison in sumif/countif

  • Thread starter Thread starter mato
  • Start date Start date
M

mato

hello,

i'm trying to make SUMIF behave in case sensitive fashion
but have failed to find out a solution to this.

i have a letter in cell G42 against i check my values as
shown below:

=IF(SUMIF(H42:BD42;G42;H$1001:BD$1001)<>0;SUMIF
(H42:BD42;G42;H$1001:BD$1001); "")

could someone point me to the right direction?

thanks!

mato
 
If I understand correctly:

=SUMPRODUCT(EXACT(G42,H42:BD42)*H1001:BD1001)

HTH
Jason
Atlanta, GA
 
Hi
try
=IF(SUMPRODUCT(EXACT(G42,H42:BD42)*(H1001:BD1001))<>0,SUMPRODUCT(EXACT(
G42,H42:BD42)*(H1001:BD1001)),"")

or as you seem to use the semicolon as delimiter:
=IF(SUMPRODUCT(EXACT(G42;H42:BD42)*(H1001:BD1001))<>0;SUMPRODUCT(EXACT(
G42;H42:BD42)*(H1001:BD1001));"")
 
hello,

there is a letter in G42, say "D", and i want to count all
the cells in H42:BD42 which contain "D", too.
unfortunately for count/sumif "D" equals "d". :-(

martin
 
hello,

unfortunately, excel won't take this. it reports an error
and highlights "G42," part of the string.

just to make it clear:
there is a letter in G42, say "D", and i want to count all
the cells in H42:BD42 in which there's "D", too.
unfortunately for count/sumif "D" equals "d". :-(

martin
 
Hi
try my solution or just replace the coma in Jasons formula to a
semicolon (you seem to use the semicolon as delimiter)
 
hello,

i haven't got much knowledge of excel, but when i tried to
enter your formula, it produced error message and excel
highlighted "G42" part.

to make it more clear:
there is a letter in G42, say "D", and i want to count/sum
all the cells in H42:BD42 in which there's "D", too.
unfortunately for count/sumif "D" equals "d". :-(

can you see somthing i might be doing wrong?

martin
-----Original Message-----
Hi
try
=IF(SUMPRODUCT(EXACT(G42,H42:BD42)*(H1001:BD1001))
Click to expand...
 
Hi
what formula delimiter do you use (',' or ';') depending on this use
one of the formulas i provided (or do both create an error)
 
well, sorry, it seems to be working. :o))
it's just that my excel doesn't accept coma as a separator.
thanks a lot!
m.
 
yeah, i've tried it and it didn't work. :o(
than i tried again and it did. :o)
i must have been doing sth wrong. ;)
anyway, it seems my excel doesn't accept coma as a
separator.
thank you very much!
m:)
 
i'm trying to make SUMIF behave in case sensitive fashion
but have failed to find out a solution to this.

i have a letter in cell G42 against i check my values as
shown below:

=IF(SUMIF(H42:BD42;G42;H$1001:BD$1001)<>0;SUMIF
(H42:BD42;G42;H$1001:BD$1001); "")

could someone point me to the right direction?
Click to expand...

You've already been told several times that you need to use EXACT to do this.
That's basically correct, but if you're comparing single character strings
against each other, you could also use CODE. That is,

=IF(SUMPRODUCT((CODE(H42:BD42&"@")=CODE(G42&"#"))*H$1001:BD$1001);
SUMPRODUCT((CODE(H42:BD42&"@")=CODE(G42&"#"))*H$1001:BD$1001);"")

The &"@" and &"#" bits avoid errors if any of the cells are blank or evaluate to
zero length strings.

Note that the other responses using EXACT should work in Excel as long as you
replace commas with semicolons.
 
Back
Top