Multiple criteria formula help please!

[candymycandy]

Hi all

I need help with a formula please.

I have an events spreadsheet:

Tab 1: A user enters event/delegate details and notes whether the event is training or a conference.
Tab 2: Financial info on how much each company has spent on tickets
Tab 3: Training and conference events have a ticket cost filled in here (to allow this to be easily amended each year).

In tab 2 I need to evaluate how much each company who sends attendees to the event has spent on a training event or a conference event, based on the name of the company x how many of each type of event they've been to. I have uploaded an example workbook which hopefully explains it better - Upload files for free - events example dummy data.xlsx - ufile.io

Any help much appreciated!

Thanks.

 

[Sanjeev1976]

You can check the below solution :

events example dummy data.xlsx
	A	B	C	D
1	Company	Conference Seats £	Training Seats £	Total £
2	Big Co	500	1,000	1,500
3	Tesco	-	1,000	1,000
4	Petro Co	-	500	500
5	Bakes and Cakes	1,000	-	1,000
6	BP	1,000	-	1,000
7	Hewlett Packard	500	-	500
company info

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(COUNTIFS(events!$E$2:$E$12,'company info'!$A2,events!$C$2:$C$12,"Conference"),0)*'event costs'!$B$1
C2:C7	C2	=IFERROR(COUNTIFS(events!$E$2:$E$12,'company info'!$A2,events!$C$2:$C$12,"Training"),0)*'event costs'!$B$1
D2:D7	D2	=SUM(B2:C2)

 

[Candyman8019]

 

[candymycandy]

This is it! Thanks so much