How to sum dashes "-" as zero?

[ellaine216]

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.

[TABLE="width: 936"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Thursday[/TD]
[TD="colspan: 2"]Friday[/TD]
[TD="colspan: 2"]Saturday[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Park[/TD]
[TD]Ticket Type[/TD]
[TD]Park[/TD]
[TD]Ticket Type[/TD]
[TD]Park[/TD]
[TD]Ticket Type[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Universal Studios[/TD]
[TD]One Park - 1 Day[/TD]
[TD]Universal Studios[/TD]
[TD]Park to Park - 1 Day[/TD]
[TD]Walt Disney World[/TD]
[TD]Magic Kingdom - 1 Day[/TD]
[TD="align: right"] $624.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Universal Studios[/TD]
[TD]One Park - 1 Day[/TD]
[TD]Universal Studios[/TD]
[TD]Park to Park - 1 Day[/TD]
[TD]Walt Disney World[/TD]
[TD]Park Hopper - 1 Day[/TD]
[TD="align: right"] $712.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Walt Disney World[/TD]
[TD]Other - 1 Day[/TD]
[TD]Universal Studios[/TD]
[TD]Park to Park - 1 Day[/TD]
[TD]Walt Disney World[/TD]
[TD]Park Hopper - 1 Day[/TD]
[TD="align: right"] $707.20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]Universal Studios[/TD]
[TD]Park to Park - 1 Day[/TD]
[TD]Walt Disney World[/TD]
[TD]Magic Kingdom - 1 Day[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]Universal Studios[/TD]
[TD]Park to Park - 1 Day[/TD]
[TD]Walt Disney World[/TD]
[TD]Park Hopper - 1 Day[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Gerald Higgins]

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.

 

[FDibbins]

I don't really see what the problem is, =SUM() ignores all text (- is text), so...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][/tr]
[tr][td]

1​

[/td][td]

1​

[/td][/tr]

[tr][td]

2​

[/td][td]

2​

[/td][/tr]

[tr][td]

3​

[/td][td]

3​

[/td][/tr]

[tr][td]

4​

[/td][td]-[/td][/tr]

[tr][td]

5​

[/td][td]-[/td][/tr]

[tr][td]

6​

[/td][td]abc[/td][/tr]

[tr][td]

7​

[/td][td]

5​

[/td][/tr]

[tr][td]

8​

[/td][td]

11​

[/td][/tr]
[/table]
A8=SUM(A1:A7)
will sum all values and exclude (aka treat as 0, in this case) all text

 

[ellaine216]

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.

 

[ellaine216]

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.

 

[philaugust2004]

Where are you looking up the price from?

 

[ellaine216]

[TABLE="width: 358"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Universal Studios[/TD]
[/TR]
[TR]
[TD]Park[/TD]
[TD]Ticket Price[/TD]
[TD]Price w/ Tax[/TD]
[/TR]
[TR]
[TD]Park to Park - 1 Day[/TD]
[TD="align: right"] $165.00[/TD]
[TD="align: right"] $264.00[/TD]
[/TR]
[TR]
[TD]Park to Park - 2 Day[/TD]
[TD="align: right"] $254.99[/TD]
[TD="align: right"] $407.98[/TD]
[/TR]
[TR]
[TD]One Park - 1 Day[/TD]
[TD="align: right"] $110.00[/TD]
[TD="align: right"] $176.00[/TD]
[/TR]
[TR]
[TD]One Park - 2 Day[/TD]
[TD="align: right"] $199.99[/TD]
[TD="align: right"] $319.98[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Walt Disney World[/TD]
[/TR]
[TR]
[TD]Park[/TD]
[TD]Ticket Price[/TD]
[TD]Price w/ Tax[/TD]
[/TR]
[TR]
[TD]Magic Kingdom - 1 Day[/TD]
[TD="align: right"] $115.00[/TD]
[TD="align: right"] $184.00[/TD]
[/TR]
[TR]
[TD]Other - 1 Day[/TD]
[TD="align: right"] $107.00[/TD]
[TD="align: right"] $171.20[/TD]
[/TR]
[TR]
[TD]Any One Park - 2 Day[/TD]
[TD="align: right"] $199.00[/TD]
[TD="align: right"] $318.40[/TD]
[/TR]
[TR]
[TD]Park Hopper - 1 Day[/TD]
[TD="align: right"] $170.00[/TD]
[TD="align: right"] $272.00[/TD]
[/TR]
[TR]
[TD]Park Hopper - 2 Day[/TD]
[TD="align: right"] $259.00[/TD]
[TD="align: right"] $414.40[/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

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?

 

[ellaine216]

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.