Which functions to use?

Hello!

I recently started learning about basic spread sheet and different functions, and been solving problems for practice. I am currently stuck on these two so any help will be appreciated!


Question 1) The following spreadsheet includes the names of various movie theatres in town. Each theatre has multiple cinemas in which various movies are played. The types of movie (Drama, Comedy, etc.) playing in each cinema are listed on the left side

Note: If no movie is playing in a cinema, the cell type is empty.

https://imageshack.com/i/eypoUYGip

The following ranges have been named:

C5:C11 -> GALAXYtype
C14:C18 -> EMPIREtype

a) What formula would you enter into F6 to count the total number of Comedy movies played at Galaxy? Your formula must be able to be filled down and right from F6 to H7 to correctly populate the table. The values shown in the summary table should be automatically updated if the types of movie showing in the cinemas change (e.g., C4 is changed to Comedy) or if the type of movie to be summarized changes (e.g., F5 is changed to Horror). (Hint: use the indirect formula)

b) What formula would you enter in cell I6 to count the number of movies playing at Galaxy? Again your formula must be able to be filled down and be automatically updated if the types of movie showing in the cinemas change. (Hint: use the COUNTA formula)


Question 2) HoneyBee Cupcake is having a promotion to attract customers. Above is a table showing different discount rates depending on the customers' birthday and membership status. The store owner may change the discount rates from time to time.

https://imageshack.com/i/iqTiu0UVp

a) In cell D8, enter the formula that would display the discount rate to be applied (The formula you entered should be able to be spread down to cell D13).

b) The store owner wants to check the discount rate that applies to each customer once he enters customer's name in cell C15 (In this case, the customer name is "Chris", but the formula entered should work for any customer's name). What formula would you insert in cell C16?

F5 has the word Comedy in it. Thanks for the help though.