sumproduct

[sureshsonti]

Hi,
I am developing an invoice template for my boss where he wants to extract data from a table based on 3 columns headers.

If Regular Hours and Overtime hours are greater than 0 he wants to display the hourly rates for Regular Hours and Overtime hours..

I have attached a sample file

Thanks for your help

 

[sureshsonti]

I used a sumproduct formula and was able to extract the numbers, but the totals in Column G shows #VALUE! error and IF I use IFERROR function i get blank values in Column G.

Also can anyone suggest if there is a better formula. I am not sure if my formula is a good one.

Please see sample output

 

[Haz]

You don't need the IF(C4 = "","", part.
When you multiply the overtime rate by 0 (hours) the total will be just (regular rate * regular hours).

 

[pecoflyer]

You get a VALUE error because your formulas in col G refer to F which contains null strings ("") when C is empty.
If you enter a value in the C cells, all will be OK

 

[sureshsonti]

You get a VALUE error because your formulas in col G refer to F which contains null strings ("") when C is empty.
If you enter a value in the C cells, all will be OK

Haz/pecoflyer
I deleted this part IF(C4 = "","", but I am still getting the #VALUE!. I understand that I have to enter a value in column C. Is it possible to leave that cell to 0 or blank and still Column G calculates the values.

Please see sample file.

Thanks for take your time to look at my problem.

 

[Haz]

Haz/pecoflyer
I deleted this part IF(C4 = "","", but I am still getting the #VALUE!. I understand that I have to enter a value in column C. Is it possible to leave that cell to 0 or blank and still Column G calculates the values.

Please see sample file.

Thanks for take your time to look at my problem.

You didn't remove it, you just added a different IF.
Your formula should start with =SUMPRODUCT, do not add a IF.

 

[John Jairo V]

Hi, to all!

Another option in file. Blessings!

 

[sureshsonti]

You didn't remove it, you just added a different IF.
Your formula should start with =SUMPRODUCT, do not add a IF.

Thanks Haz. That works. Thanks again

 

[sureshsonti]

Hi, to all!

Another option in file. Blessings!

Thanks John. I initially wanted to use index and match. Tried a lot of things for a day and ended up with sumproduct formula.

Thanks again

 

[sureshsonti]

Hi, to all!

Another option in file. Blessings!

John,
What does the asterisk mean in this formula

=IF(D4="","",(B4>0)*VLOOKUP($D4,rates,MATCH(E$3,$I$3:$K$3,),))

Thanks

 

[John Jairo V]

John,
What does the asterisk mean in this formula

The asterisk represents multiplication. When you use a mathematical operation with a true or false, this are converted to 1's and 0's.
In other words:

if B4>0 is true, multiplies 1 by the result of VLOOKUP.
if B4>0 is false, multiplies 0 by the result of VLOOKUP.

Blessings!