DSum Criteria Issue

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,368
Hello all,

I have a list of data with salesperson and qty. (Excel 2003)

I am using dsum and it was working great until I add a new sales man who happened to be the son of another salesman.

example.
when I just had Bill Gates his total (DSUM) was fine.
now I added Bill Gates Jr.
since then Bill Gates totals now include Bill Gates AND Bill Gates Jr.
FYI - Bill Gates Jr. totals are correct.
anyone have a fix?

Ross
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi Ross

If you write in the criteria

Bill Gates

it will match anything starting with Bill Gates. To match only exactly Bill Gates, write in the criteria instead the formula

="=Bill Gates"
 
Upvote 0
I see that putting the ="=Bill Gates" fixes the issue.

Unfortunately my list of names is a drop down of about 200 people from a list on another sheet.
Is there another way to "Fix" this issue?

Ross
 
Upvote 0
I'm getting there. Now trying to write the criteria with VBA

this works
Code:
Range("R2") = "=""=Bill Gates"""

But I can't get it when I use a cell referance
Code:
Range("R2") = "=""=" & RANGE("A1") &"""

any one have the syntax I need with all these = signs and " going on?
 
Upvote 0
It works fine unless I have an entry with a quote in it.

Ex. if A1 is 1/2" Socket then I get an error in the code.
Any work around for this?

Thanks to all,

Ross
 
Upvote 0
Hi Again

Since to specify a double quote inside a doublequoted string you have to double it, try:

Code:
Range("R2").Formula = "=""=" & Replace(Range("A1"), """", """""") & """"

( 22 " in the statement :confused: )
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top