SumIFs based on multiple conditions and multiply by a value if certain text present

[questforexcel]

Hi All,

I am hoping you could help on this one.

I need my formula to search 2 columns in excel.

One column is based on the asset type. The other is based on the currency.

I need my formula to return the total by asset type and in SGD equivalent currency.

From my larger data set, i am trying this formula = SUMIFS($B$35:$T$39,L$35:L$39,$C8,I$35:I$39,"USD",I$35:I$39,"CAD",I$35:I$39,"GBP") but it returns the value error.

The above formula is not complete. I want my formula to multiply by the USD FX rate, if asset is in USD, GBP FX rate, if in GBP and CAD FX Rate, if in CAD.

Could you please help guide me on how to use a formula which can sum if based on the multiple criterias and multiply the individual USD, CAD, GBP numbers while summing them up. So that I can arrive at the SGD Equivalent number.

Is this possible?

I am not able to download or enable XL2BB on laptop. Apologies for any inconvenience.

						USD	1.35​
						GBP	1.67​
						CAD	1.00​
Asset Type	Location	Name of Asset	Currency	Amount	SGD
Machinery	New Jersey	Generator	USD	135,000	100,000
Machinery	Minneapolis	UPS	USD	270,000	200,000
Laptop	London		GBP	167,000	100,000
Buildings	Manchester	Tower	GBP	334,000	100,000
Land	Ottawa		CAD	200,000	200,000
			Total in Reporting Currency	1,106,000
			Total in SGD Equivalent	700,000

 

[questforexcel]

Thank you @ExceLoki and @Sufiyan97 for your kind help and efforts.

I wanted to mark both your answers as solutions but did not have that option.

This saved me a great deal of adding unnecessary columns and helps summarize my data quickly.

Can I ask you one last bit on this?

Is there a way to add a hlookup reference within the sum range column of the sumifs formula. So like how its currently manually selected the YTD column, i want the sum range to be driven by referencing column headers that match the summary table column headers. So match YTD Total to the YTD Total in Summary, Next Year Outlook column to Next Year Outlook in Summary.

The reason for doing that, is that I noticed, i had a few oversights at my end of referencing to the wrong column. I want to avoid that.

Thank you,

 

[questforexcel]

Thank you for the update. Apologies @ExcelLoki. Thank you for your help and guidance.

@questforexcel you should mark @ExceLoki 's formula in post #9 as a solution.

 

[ExceLoki]

no worries, happy to help

 

[ExceLoki]

i dont see those in the post, so it's hard to build it for that. but assuming you mean having titles in row 1 that match the titles in row 6