Google Sheets Sumifs from multiple columns

[jl2509]

Hi Everyone,
I am trying to get google sheets to perform a lookup from multiple columns but cannot get the true sum to show?

In column B there are "Sites" in Column C there are "Departments" where 2 criteria is required to be filtered, in column D there are "Roles" and in column E there are Costs to be summed.

I have attached an image to support this.
Any help in creating something like a Sum, Sumifs, Sumproduct formula would be greatly appreciated.

 

[Sufiyan97]

Try

=SUMIFS(E:E,B:B,"London",C:C,"*Design",D:D,"Projects")

 

[jl2509]

Hi,

Thanks for the response

Apologies, I made a mistake in the sample data, the "R&D Design" should only be "R&D" so the solution would not work.

 

[Sufiyan97]

Try

=SUM(SUMIFS(E:E,B:B,"London",C:C,{"Design","R&D"},D:D,"Projects"))

 

[jl2509]

That is exactly what I have and it does not calculate both "Design" and "R&D", to test this, if I omit "R&D" I get 5000, if I omit "Design" I get 5000 but both together or reversed I get 5000?

 

[Sufiyan97]

Try

=SUMPRODUCT(E2:E100*(B2:B100="London")*((C2:C100="Design")+(C2:C100="R&D"))*(D2:D100="Projects"))

 

[jl2509]

and the winner is Sufiyan97

Great solution thank you so much

 

[Sufiyan97]

You're welcome
Glad I could help.