Cell to give me total amount depending on multiple criteria in seperate sheet

[Lmaonade]

Hi, I'm trying to create a table here that gives me a total amount depending on certain things.

So for example:

This is my sheet that I want to totals to go onto. It's called "January Costs"

This is the table that would have the information, the sheet is called "Interviews":

I want B4 in my first sheet to return the sum of anything in the table that has their name as the consultant and if the G column says "Passed" only for dates in January in the D column, multiplied by the cost in F1. So for this I would want B4 to return £100.00 as for the january dates - Ade, Helen, Nicola and Evangeline are all in his name, all passed in January meaning 4 x £25.00.

I'm probably over complicating things but I would like to create this table every month to make it easier as there is going to be a lot of data.

I'd appreciate any help.

Regards,
Andy

 

[Eric W]

How about:

=SUMPRODUCT(--(Interviews!$B$4:$B$100=A4),--(Interviews!$G$4:$G$100="Passed"),--(MONTH(Interviews!$D$4:$D$100)=MONTH(1&$A$1)))*Interviews!$F$1

 

[dnorm]

Sorry, my method did not take a month filter into account. - deleted

 

[Lmaonade]

How about:

=SUMPRODUCT(--(Interviews!$B$4:$B$100=A4),--(Interviews!$G$4:$G$100="Passed"),--(MONTH(Interviews!$D$4:$D$100)=MONTH(1&$A$1)))*Interviews!$F$1

That worked perfect! Appreciate it!

What if there were different costs for different people.

For example if I added into colomn H the cost rather than the cost in F1. So the cost for Ade was £25.00, the cost for Helen was £30.00. How could I change that formula to grab the different costs from the relevant cells?

Thanks again!
Andy

 

[Eric W]

That would just be:

=SUMPRODUCT(--(Interviews!$B$4:$B$100=A4),--(Interviews!$G$4:$G$100="Passed"),--(MONTH(Interviews!$D$4:$D$100)=MONTH(1&$A$1)),Interviews!$H$4:$H$100)