Help with the kind of function and syntaxis

[wolfje]

Hi guys, I'm a relatively basic excel user, and now I'm faced with an issue that I'm sure it's fairly simple to resolve, I just can't figure it out.
I have this spreadsheet where I'm given just the name of the passengers, how many days they'll stay and which kind of room they'll use. Also, I'm given the price of each kind of room, the exchange rate between NZD and USD and taxes.

So according to this spreadsheet, which function should I use in other for me to automatically get Net USD, Net NZD, Total USD and Total NZD just with the data given?

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]Passenger[/TD]
[TD]Days[/TD]
[TD]Kind of room[/TD]
[TD]Net USD[/TD]
[TD]Net NZD[/TD]
[TD]Total USD[/TD]
[TD]Total NZD[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]7[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]14[/TD]
[TD]Double[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]5[/TD]
[TD]Double[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]4[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]NZD 1[/TD]
[TD]USD 0.72[/TD]
[/TR]
[TR]
[TD]GST[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]Single Room[/TD]
[TD]50 NZD [/TD]
[/TR]
[TR]
[TD]Double Room[/TD]
[TD]85 NZD[/TD]
[/TR]
</tbody>[/TABLE]

I tried several functions including IF, SUMIF, SUMINFS with no luck, at best I've received 0 as a result.
Please, please give me a hint about what should I do in this case.

Thank you so much in advance

 

[jtakw]

Hi,

It sounds like some kind of assignment or homework, Pardon me if I'm wrong.
So what I can offer is, if you can change the structure of the 2nd table to look like below, where the Numbers are separated from the Texts, then there are a variety of functions/formula you can use.
A couple of suggestions would be SUMIF or SUMIFS and other basic math, VLOOKUP and math.

Excel 2010
	A	B	C	D	E	F	G
1	Passenger	Days	Kind of room	Net USD	Net NZD	Total USD	Total NZD
2	A	7	Single
3	B	14	Double
4	C	5	Double
5	D	4	Single
6
7	NZD	1		USD	0.72
8	GST			15%
9	Single			50	NZD
10	Double			85	NZD
Sheet4

 

[shg]

Cross-posted at https://www.excelforum.com/excel-general/1228217-help-with-the-kind-of-function-and-syntaxis.html

 

[wolfje]

In a way it is? We've been using this spreadsheet for a while, but we input the numbers by hand, I mean at most =sum(price*how many days) and then the conversion into USD, but my management has tasked me to find a better and faster way. Sadly I can't just figure it out. I'm fairly sure once someone tells me you should use this function and this syntaxis I'll feel very dumb.

 

[jtakw]

In a way it is? We've been using this spreadsheet for a while, but we input the numbers by hand, I mean at most =sum(price*how many days) and then the conversion into USD, but my management has tasked me to find a better and faster way. Sadly I can't just figure it out. I'm fairly sure once someone tells me you should use this function and this syntaxis I'll feel very dumb.

Thank you for clarifying, doesn't sound like Homework.
As pointed out by shg, you cross posted at Excelforum, might want to take a look here (especially #13 ): https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

You posted/uploaded a sample Workbook there, but I have no access to it since I'm not a member of that site.
So I've made some modifications to the sample posted here to make the formulas easier to manage, if the table can not be changed, then you can adjust the formulas to suit, or if you need further help, post an actual sample of the exact layout of your Workbook here.

Excel 2010
	A	B	C	D	E	F	G
1	Passenger	Days	Kind of room	Net USD	Net NZD	Total USD	Total NZD
2	A	7	Single	252	350	289.8	402.5
3	B	14	Double	856.8	1190	985.32	1368.5
4	C	5	Double	306	425	351.9	488.75
5	D	4	Single	144	200	165.6	230
6
7	USD	NZD
8	0.72	1
9	GST	15%
10	Single	50	NZD
11	Double	85	NZD
Sheet4

Cell Formulas
Range	Formula
D2	=SUMIF($A$10:$A$11,$C2,$B$10:$B$11)*A$8*$B2
F2	=SUMIF($A$10:$A$11,$C2,$B$10:$B$11)*A$8*$B2*(1+$B$9)

D2 formula copied down and across to E5
F2 formula copied down and across to G5

When there're any changes to Room rates, Tax rate, Conversion rate, just change the figures in A8:B11

 

[wolfje]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Passengers[/TD]
[TD]Days[/TD]
[TD]Kind of Room[/TD]
[TD]Net USD[/TD]
[TD]Net NZD[/TD]
[TD]Total NZD[/TD]
[TD]Total USD[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]a[/TD]
[TD]6[/TD]
[TD]Double[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]b[/TD]
[TD]8[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]c[/TD]
[TD]24[/TD]
[TD]Double [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]d[/TD]
[TD]9[/TD]
[TD]Double [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]e[/TD]
[TD]17[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]h[/TD]
[TD]20[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]w[/TD]
[TD]3[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]x[/TD]
[TD]15[/TD]
[TD]Double [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]y[/TD]
[TD]15[/TD]
[TD]Double [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On a separate sheet I had the following indicators:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1 NZD[/TD]
[TD]0.72 USD[/TD]
[/TR]
[TR]
[TD]GST[/TD]
[TD]15%
[/TD]
[/TR]
[TR]
[TD]Double[/TD]
[TD]85 NZD[/TD]
[/TR]
[TR]
[TD]Single [/TD]
[TD]50 NZD[/TD]
[/TR]
</tbody>[/TABLE]

Finally used:

=IF(Kind_of_room="Double";SUM(Days*Ref!$B$3);IF(Kind_of_room="Single";SUM(Days*Ref!$B$4))) In order to obtain Net NZD and the rest was an easy use of SUM

Took me well over 8 hours.
Thanks for your help, I do really appreciate it.