Case Sensative Subtotaling
 

I have a situation where I have multiple contracts which are 2 characters and
are case sensative.

Example....

Contract #1 = 'AE' (both uppercase)
Contract #2 = 'aE' (lowercase a, uppercase E)
Contract #3 = 'Ae' (uppercase A, lowercase e)

Each contract has multiple lines of sales dollars and I need to sum
according to each specific contract, maintaining the case sensativity. Is
there a way, without changing the characters, to do this??

Case Sensative Subtotaling
 

Here is a brute force method.
To sum the B column when A column values AE use
=SUMPRODUCT(--(CODE(LEFT(A1:A8))=65),--(CODE(RIGHT(A1:A8))=69),B1:B8)

The codes are
a 97
e 101
A 65
E 69


To do it without specifying codes (more flexible):
In D1 I have the text AE
In D2 I used
=SUMPRODUCT(--(CODE(LEFT(A1:A8))=CODE(LEFT(D1))),--(CODE(RIGHT(A1:A8))=CODE(RIGHT(D1))),B1:B8)


best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"wingsdihard2" wrote in message
...
I have a situation where I have multiple contracts which are 2 characters
and
are case sensative.

Example....

Contract #1 = 'AE' (both uppercase)
Contract #2 = 'aE' (lowercase a, uppercase E)
Contract #3 = 'Ae' (uppercase A, lowercase e)

Each contract has multiple lines of sales dollars and I need to sum
according to each specific contract, maintaining the case sensativity. Is
there a way, without changing the characters, to do this??

Case Sensative Subtotaling
 

wingsdihard2 wrote:
I have a situation where I have multiple contracts which are 2 characters and
are case sensative.

Example....

Contract #1 = 'AE' (both uppercase)
Contract #2 = 'aE' (lowercase a, uppercase E)
Contract #3 = 'Ae' (uppercase A, lowercase e)

Each contract has multiple lines of sales dollars and I need to sum
according to each specific contract, maintaining the case sensativity. Is
there a way, without changing the characters, to do this??

With data in columns A (contract numbers) and B (dollars), put 'AE' in E1 and
the following array formula (commit with CTRL+SHIFT+ENTER) in F1:

=SUM(IF(EXACT(E1,$A$2:$A$12),$B$2:$B$12,0))

Case Sensative Subtotaling
 

Try one of these formula

=SUMPRODUCT(--ISNUMBER(FIND("aE",A1:A12)),B1:B12)
or
=SUMPRODUCT(--(EXACT(A1:A12,"aE")),B1:B12)


"wingsdihard2" wrote:

I have a situation where I have multiple contracts which are 2 characters and
are case sensative.

Example....

Contract #1 = 'AE' (both uppercase)
Contract #2 = 'aE' (lowercase a, uppercase E)
Contract #3 = 'Ae' (uppercase A, lowercase e)

Each contract has multiple lines of sales dollars and I need to sum
according to each specific contract, maintaining the case sensativity. Is
there a way, without changing the characters, to do this??