Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    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. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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. #4
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    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. #5
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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
  •