DSUM Partial Match
 

I have a list of companies in col A and Revenues in column B. There are
several rows with revenue for the same comapny so I want to use DSUM to get a
total revenue per company.

My problem is that DSUM adds together all revenues fo companies that begin
with the same letters even if though there is an exact match. For example,
if my company name criteria is "A", DSUM adds together companies A, AB, ABC,
Acme, etc.

Does anyone know a way to force DSUm to inly sum an exact match? Thanks

DSUM Partial Match
 

Use this criteria for A


="=A"

instead of just A

note that it is not case sensitive so it will sum for "a" as well

if you need case sensitive remove the header from the criteria range (leave
it blank)
and use

=EXACT("A",A5)


where A5 is the first cell in the company column with data meaning the
header would be in A4



Note that you can use


=SUMIF(Company_Range,"A",Revenue_Range)


or


=SUMPRODUCT(--(Company_Range="A"),Revenue_Range)


the D functions are a bit archaic



--
Regards,

Peo Sjoblom






"Bob H" wrote in message
...
I have a list of companies in col A and Revenues in column B. There are
several rows with revenue for the same comapny so I want to use DSUM to
get a
total revenue per company.

My problem is that DSUM adds together all revenues fo companies that begin
with the same letters even if though there is an exact match. For
example,
if my company name criteria is "A", DSUM adds together companies A, AB,
ABC,
Acme, etc.

Does anyone know a way to force DSUm to inly sum an exact match? Thanks

DSUM Partial Match
 

Bob

Try making your criteria

="=A"

to keep from including AB and ABC in with your A's. I am not saying
it is logical, but, I am pretty sure it will work. You may also like
a pivot table or a sumproduct formula to get what you want.

Good luck.

Ken
Norfolk, Va.




On Jul 27, 4:38 pm, Bob H wrote:
I have a list of companies in col A and Revenues in column B. There are
several rows with revenue for the same comapny so I want to use DSUM to get a
total revenue per company.

My problem is that DSUM adds together all revenues fo companies that begin
with the same letters even if though there is an exact match. For example,
if my company name criteria is "A", DSUM adds together companies A, AB, ABC,
Acme, etc.

Does anyone know a way to force DSUm to inly sum an exact match? Thanks

DSUM Partial Match
 

Thank you both very much for the quick and accurate solution

"Bob H" wrote:

I have a list of companies in col A and Revenues in column B. There are
several rows with revenue for the same comapny so I want to use DSUM to get a
total revenue per company.

My problem is that DSUM adds together all revenues fo companies that begin
with the same letters even if though there is an exact match. For example,
if my company name criteria is "A", DSUM adds together companies A, AB, ABC,
Acme, etc.

Does anyone know a way to force DSUm to inly sum an exact match? Thanks