Multi-conditioned revenue function

[JavierDeMuller]

Hi,

I am having a problem with a function to calculate the revenue of a certain activity.

I have a list of contacts, which tells if they have attended and dined or only attended to the activity. If they attend and dine, they pay 4€, else, if they attend they pay 2€. The list has the next structure:

Person	Type
Person 1	Attended/Attended and Dined/Not attended
Person 2	...

I have made a list of the people who attend to the dinner, with the function filter, and I have a box next to it to confirm the payment.

So, I have two tables: one with the information of attendance and another with the payment confirmation of those who attended.

I would like to make a function which returns the total revenue. My idea was taking the people who attended and payed, and filter which ones, in the other list have attended and dined, multiply that by 4 and then take the rest and multiply by 2. The problem is that I don't know how to take the list of people who attended and payed and check in the other list which ones dined. The problem is more complex and bigger, that is why I can't just merge the two lists, which would make it much easier.

I have attached an example which may clarify better what I want to do.

Thanks for the help

I am using Office 365 on Windows

[#2 · Accepted solution]

@JavierDeMuller 

You can use VLOOKUP to find out who just attended and who attended and dined, then use COUNTIFS to return the amount you want. See the attached version.

[#3]

Thank you for the response. I wonder still if there would be a way to circumvent using a row for the type next to payments, although for my use it actually makes more sense for it to exist, so I am using your answer.