Formula Inconsistent

R

Roadking

Board Regular
Joined
Feb 15, 2014
Messages
78
Windows 7 Professional
Excel 2013

Good afternoon,

Trying to understand why my formula works on certain words and not others. My target worksheet, DB_Activity Col - $D$2:$D$1000 consists of 36 “Expense Descriptions” and Col - $E$2:$E$1000 consist of the cost associated each Description. The following formula works fine with the below list with the exception of those with an **.
=SUMIF(DB_Activity!$D$2:$D$1000,"Other",DB_Activity!$E$2:$E$1000)

** Accounting Service
** Advertising
Auto Lease Insurance
Auto Lease Payment
Auto Maintenance
Bank Fees
Bank Supplies
Cell Phone
Computer Hardware
Computer Software
Corporate Fees
Credit Cards
Deposit
Entertainment
Fuel
Gifts
Internet/High Speed
Laundry/Dry Cleaning
Legal
** Licensing
Lodging
Loan
Office Equipment
Other
Parking
Personal Expenses
** Programming
Seminar's / Training
Service Contracts
Supplies
Taxes
Tolls
Travel
Unexpected Expenses
Web-Design
Web-Service

Any help greatly appreciated
John
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Can you give examples of the ones that DO work, and some that DON"T ?
And describe exactly what you mean that doesn't work.
Do you get an error? What error?
Do you get the wrong result ? MORE or LESS than you expected?

My first guess would be that it's related to the ** in some of the strings.
Countif will use those ** as wildcards.
 
Upvote 0
@Roadking

Try to switch to a different type of formula for SUMIFF(S) is sensitive to the presence of wildcards like *.

=SUMPRODUCT(DB_Activity!$E$2:$E$1000,--(DB_Activity!$D$2:$D$1000="Other"))
 
Upvote 0
looks like the data that is posted also has spaces at the end of the entries with the double *
 
Upvote 0
** is just to indicate to you what words are not recognized.
Example: =SUMIF(DB_Activity!$D$2:$D$1000,"Advertising",DB_Activity!$E$2:$E$1000) NO error message just returns a 0.00 value instead of the actual value associated with Advertising
 
Upvote 0
Either there are no exact matches for "Advertising" in column D
Do you get a correct COUNT with countif instead?
=COUNTIF(DB_Activity!$D$2:$D$1000,"Advertising")


Or
Perhaps then some of the numbers in DB_Activity!$E$2:$E$1000 are not really numbers at all. But 'Numbers Stored As Text'

Does this return the expected result?
=SUMPRODUCT(DB_Activity!$E$2:$E$1000+0,--(DB_Activity!$D$2:$D$1000="Advertising"))
 
Upvote 0
Roadking said:
** is just to indicate to you what words are not recognized.
Example: =SUMIF(DB_Activity!$D$2:$D$1000,"Advertising",DB_Activity!$E$2:$E$1000) NO error message just returns a 0.00 value instead of the actual value associated with Advertising
Click to expand...

Great.

What do you get with?

=SUMPRODUCT(--(TRIM(DB_Activity!$D$2:$D$1000)="Advertising"),DB_Activity!$E$2:$E$1000)

=SUMPRODUCT(--(TRIM(DB_Activity!$D$2:$D$1000)="Advertising"),--(DB_Activity!$E$2:$E$1000))
 
Upvote 0
Thank you Aladin, both formulas seem to work. However, I'm still baffled why my formula worked fine for some and not for others.
 
Upvote 0
It's because you had a trailing space on some of your entries - "Advertising " wasn't matching "Advertising"
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
OR - formula is giving #value error
Replies
6
Views
99
etaf
etaf
charlesstricklin
  • Solved
Create drop-down menu items from a list of comma-delimited text?
Replies
1
Views
206
alansidman
A
Craig Peter DG
  • Solved
Simple Problem, Tricky Solution
2
Replies
16
Views
450
Georgiboy
Georgiboy
A
  • Solved
index match using column headers
Replies
4
Views
221
AlanY
A
Sufiyan97
  • Solved
Convert Formula to VBA
2
Replies
11
Views
873
Alex Blakenburg
A

Forum statistics

Threads
1,214,567
Messages
6,120,268
Members
448,953
Latest member
Dutchie_1

Share this page

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