Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I want a formula which says if x =" y" or "z" or "whatever" multiple a by either "x" or "y" but get a VALUE error- any ideas? =PRODUCT(IF('Historical data'!I5="Research Associate",Costs!$B$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Senior Research Associate A",Costs!$C$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Senior Research Associate B",Costs!$D$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Freelancers",Costs!$E$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Research Director",Costs!$F$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Company Director",Costs!$G$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Managing Director",Costs!$H$18*'Historical data'!F5),"")))))))
Solved by V. Y. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 21/08/2017 - 03:04
Welcome, Thanks for choosing Got It Pro-Excel.
Excelchat Expert 21/08/2017 - 03:05
I understand that you need help with formulas
User 21/08/2017 - 03:06
yes please
User 21/08/2017 - 03:06
i'm struggling with a product if formula
User 21/08/2017 - 03:06
=PRODUCT(IF('Historical data'!I5="Research Associate",Costs!$B$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Senior Research Associate A",Costs!$C$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Senior Research Associate B",Costs!$D$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Freelancers",Costs!$E$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Research Director",Costs!$F$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Company Director",Costs!$G$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Managing Director",Costs!$H$18*'Historical data'!F5),"")))))))
Excelchat Expert 21/08/2017 - 03:07
Can you put some samples in the sheet?
User 21/08/2017 - 03:07
keep returning #VALUE result - any ideas where ive gone wrong?
Excelchat Expert 21/08/2017 - 03:08
Are you currently working on MS Excel or Google Sheets?
User 21/08/2017 - 03:09
excel
User 21/08/2017 - 03:09
cant copy it into google sheets it keeps saying reconnecting
Excelchat Expert 21/08/2017 - 03:09
Hmm. even just simple values
Excelchat Expert 21/08/2017 - 03:09
so we can at least replicate what you have in MS Excel.
User 21/08/2017 - 03:09
waiting for it to stop saying reconnecting
User 21/08/2017 - 03:10
at the moment it wont let me type anything in the cell
User 21/08/2017 - 03:10
I will try and explain by text
Excelchat Expert 21/08/2017 - 03:10
Got it
User 21/08/2017 - 03:11
cell A3 may contain a value Research Analyst or Company Director for example
User 21/08/2017 - 03:11
call B4 would contain the associated day cost for research analyst
User 21/08/2017 - 03:11
cell b5 would contain teh associated day cost for company director
User 21/08/2017 - 03:11
ok so far?
Excelchat Expert 21/08/2017 - 03:12
https://docs.google.com/spreadsheets/d/1etupYGaMZ_WO9HSpGQhLlvCKpgOTy2v__w81Fz-LH7o/edit?usp=sharing
User 21/08/2017 - 03:12
cell d3 contains the number of days used on a project
Excelchat Expert 21/08/2017 - 03:12
Try that link it's the same sheet.
User 21/08/2017 - 03:14
have put some data into it
Excelchat Expert 21/08/2017 - 03:14
Ok
User 21/08/2017 - 03:14
so I want to be able to calculate the actual cost based on the resource type used and their associated day rate
User 21/08/2017 - 03:15
in my real sheet there are five different resource types
User 21/08/2017 - 03:15
feels like it should be easy but i'm doing something daft!
Excelchat Expert 21/08/2017 - 03:16
Is Column E resource type?
User 21/08/2017 - 03:17
yes thats the actual resource used
Excelchat Expert 21/08/2017 - 03:17
Associated day rate is which column?
User 21/08/2017 - 03:18
column B
Excelchat Expert 21/08/2017 - 03:19
Can you see my chat from the sheets on the upper-right part of the screen?
Excelchat Expert 21/08/2017 - 03:20
https://docs.google.com/spreadsheets/d/1etupYGaMZ_WO9HSpGQhLlvCKpgOTy2v__w81Fz-LH7o/edit?usp=sharing
Excelchat Expert 21/08/2017 - 03:20
We are running out of time. Please click this link where we can continue chatting for a few minutes after the timer runs out.
Excelchat Expert 21/08/2017 - 03:20
Reply to the chat bubble located in the upper-right corner of that page.
Excelchat Expert 21/08/2017 - 03:22
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon.
Excelchat Expert 21/08/2017 - 03:22
Please click the link.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.