# Thread: Help needed with a formula combining lookup and if statement - Excel 2010

1. ## Help needed with a formula combining lookup and if statement - Excel 2010

I need some help with a formula in a table I am developing to record the results of site audits.

This is best shown by an attachment, and I have highlighted in yellow the column I want help with. I have entered figures where I have manually calculated the answer I want to appear.

The site audits have a number of categories, each of which has a maximum possible score. The maximums are shown in row 3, and total 100.

The results of the audits will be recorded in row 5 onwards.

The complication is that not every category applies to every job, so some categories for a job have a "N/A" rather than a numeric score.

I want my formula to calculate the total possible score for a job (by reference to the values in row 3), but only for the categories for which it has a numeric answer (ie. ignoring those categories with N/A.

Thanks for your help!

Neil

2. Hi neil

Perhaps the SUMIF, in K5 & copy down!

=SUMIF(B5:I5,"<>N/A",\$B\$3:\$I\$3)

3. ## The Following User Says Thank You to Kevin@Radstock For This Useful Post:

neil (2015-11-10)

4. That's brilliant - thank you Kevin!

Incidentally, rather than saying if the cell in the range B5:I5 does not equal N/A,

is there a way to say

if the cell in the range B5:I5 contains a number?

Thanks again

Neil

5. Hi neil

=SUMIF(B5:I5,">0",\$B\$3:\$I\$3)

6. ## The Following User Says Thank You to Kevin@Radstock For This Useful Post:

neil (2015-11-10)

7. Thank you Kevin. That's perfect!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•