Need to multiply and add if numbers exist.

[SheilaWillis71]

Hi there, long time no post, need some help please. My table looks something like:

A B C D E
1 Hourly Rate Hours Daily Rate Days Total
2 NA 300 1

So what I want to say is: If you see numbers, then work with them, if not a number - skip it. Kind of like:
=Cell A2 x B2 + C2 x D2. However, this does not work because A2 says NA and B2 is blank, so I get #N/A.

Which is the same as:
Hourly rate x hours + daily rate x days = total

In this case, there is no hourly rate and therefore NA (not #N/A) is in the cell and the Hours cell is blank, but there is a daily rate and we used the item for only 1 day.

Thanks much
Sheila

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[SheilaWillis71]

In addition, I'll need this to work on excel 2010 and 2013.

 

[Sahak]

Hi Sheila,

You can use this:

=(SUM(A2)*SUM(B2))+(SUM(C2)*SUM(D2))

 

[Peter_SSs]

Welcome back!

Not sure if it would ever be possible to have numbers in both sections, or what result you want if that does happen, but this may also work for you.

Excel Workbook
	A	B	C	D	E
1	Hourly Rate	Hours	Daily Rate	Days	Total
2	NA		300	1	300
3	40	8	3	5	335
4					0
5	40	8			320
6			290	3	870
7	35	8.5	NA		297.5
8	NA		NA		0
Ignore text values

 

[Aladin Akyurek]

@Peter_SSs

We could do it directly with PRODUCT...

=PRODUCT(A2:B2)+PRODUCT(C2:D2)

 

[Peter_SSs]

We could do it directly with PRODUCT...

Good point, thanks.

 

[SheilaWillis71]

Thanks folks, the IFERROR formula worked like a charm. I am very appreciative that this forum exists. Thank you again.