How to sum dashes "-" as zero?

ellaine216

New Member
Joined
Apr 29, 2017
Messages
5
Hello, guys, I'm currently having the final assignment of my semester and I'm having difficulty when it comes to the part to adding up dashes as zeros.

ThursdayFridaySaturdayTOTAL
ParkTicket TypeParkTicket TypeParkTicket Type
Universal StudiosOne Park - 1 DayUniversal StudiosPark to Park - 1 DayWalt Disney WorldMagic Kingdom - 1 Day $624.00
Universal StudiosOne Park - 1 DayUniversal StudiosPark to Park - 1 DayWalt Disney WorldPark Hopper - 1 Day $712.00
Walt Disney WorldOther - 1 DayUniversal StudiosPark to Park - 1 DayWalt Disney WorldPark Hopper - 1 Day $707.20
--Universal StudiosPark to Park - 1 DayWalt Disney WorldMagic Kingdom - 1 Day#N/A
--Universal StudiosPark to Park - 1 DayWalt Disney WorldPark Hopper - 1 Day#N/A

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

There are two tables more which I have to use INDEX MATCH to look up the values and add them up, but the formula gives me the result when it comes to the cells including "-".
Please give me some ideas of how to solve this problem. Keep in mind that if I change the Park and Ticket type of any other days into to dashes and insert value in Thursday' cells, the formula should still working for it.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi, welcome to the board.

I'm sorry but it's totally unclear to me what exactly you want to do.

Where are the cells that include "-" ?

The only "-" characters that I can see are in the Ticket Type column.

Don't worry about explaining the real world application of what you're trying to do, just focus on describing the data, and what exactly you want to do with it.
 
Upvote 0
I don't really see what the problem is, =SUM() ignores all text (- is text), so...
A​
1​
1​
2​
2​
3​
3​
4​
-
5​
-
6​
abc
7​
5​
8​
11​
A8=SUM(A1:A7)
will sum all values and exclude (aka treat as 0, in this case) all text
 
Upvote 0
Like I said, I have to use INDEX MATCH to Look up the price depending on the park and the ticket type and sum up the price to give a total.
Now, the case is that park and ticket type is with "-", however, I have to ignore the "-" which is Thursday and sum up the prices of Friday and Saturday. BUT, if I insert the value like universal studio, one park - 1 day, the formula should sum the price of thursday +friday+saturday.
 
Upvote 0
Please check the table, the cells are not in numbers but text and I mentioned that I need to use INDEX MATCH to look up for the price and add the price.
 
Upvote 0
Universal Studios
ParkTicket PricePrice w/ Tax
Park to Park - 1 Day $165.00 $264.00
Park to Park - 2 Day $254.99 $407.98
One Park - 1 Day $110.00 $176.00
One Park - 2 Day $199.99 $319.98
Walt Disney World
ParkTicket PricePrice w/ Tax
Magic Kingdom - 1 Day $115.00 $184.00
Other - 1 Day $107.00 $171.20
Any One Park - 2 Day $199.00 $318.40
Park Hopper - 1 Day $170.00 $272.00
Park Hopper - 2 Day $259.00 $414.40

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Please check the table, the cells are not in numbers but text and I mentioned that I need to use INDEX MATCH to look up for the price and add the price.

Are you trying to say you want the formula to ignore the price if it is on a row where the "-" appears under the day name and Ticket Type? And did you say this is homework?
 
Last edited:
Upvote 0
Yes, that's what I want to do so.
And no, it's not a homework but a practice assignment and this part is what I have missing.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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