## [Solved] Product If help

### [Solved] Product If help

Hello,
I am trying to return a product with multiple ranges of criteria.

When producing a construction estimate, I am trying to have the labor rates auto calculate per task division, based on the specific crew cost. ie: Admin = \$65.00/hr ; Lead = \$60.00/hr; Laborer = \$45.00/hr and so on. This way, whatever crew type I input into the cell, it will multiply the hours estimated by the appropriate rate, and return that number in the proper cell of the labor rate column.

Specific example, if A1:A100 = "crew label" in range D3:D9 then multiply B1* by associated "crew rate" in the range E3:E9.

Does that make sense?
Last edited by Umphridas on Tue Apr 30, 2019 5:48 pm, edited 2 times in total.
OpenOffice 4.1.6 on Windows 10
Umphridas

Posts: 3
Joined: Mon Apr 29, 2019 11:07 pm

### Re: Product If help

If I am understanding your problem correctly, you might find that VLOOKUP will be a better solution.

If that doesn't help, perhaps you could upload a sample spreadsheet showing examples of what you are trying to achieve. [Forum] How to attach a document here Note maximum file size is 128K.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 2873
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: Product If help

Umphridas wrote:Specific example, if A1:A100 = "crew label" in range D3:D9 then multiply B1* by associated "crew rate" in the range E3:E9.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3779
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Product If help

So, VLOOKUP seems like it should work with regard to finding the data, but I'm not seeing how to get the final equation in there. I've uploaded an example sheet to (hopefully) better demonstrate what I'm trying to achieve.

Also, I thank you very much for taking the time to help!!
Attachments
EstimatorHelpSheet.ods
OpenOffice 4.1.6 on Windows 10
Umphridas

Posts: 3
Joined: Mon Apr 29, 2019 11:07 pm

### Re: Product If help

The formula in D6 should be
Code: Select all   Expand viewCollapse view
`=B6*VLOOKUP(C6;\$H\$9:\$I\$14;2;0)`

You should be able to copy that down the column.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7224
Joined: Sat Nov 08, 2008 8:08 pm

### Re: Product If help

It goes BEFORE VLOOKUP!!
That seems so easy when you show it.

THANK YOU!!
OpenOffice 4.1.6 on Windows 10
Umphridas

Posts: 3
Joined: Mon Apr 29, 2019 11:07 pm