Formula Error #Value!

Posted on 2011-10-25
Last Modified: 2012-05-12

I have been lumped with a spreadsheet to up keep and I am having trouble with a few of the formulas.

The error is in workbook 'Daily Schedule'

In cells AF4 - AL4, the formula is working however when I click into it the #Value! appears in the cell. This is the formula below:
=SUM(('Daily Schedule'!$F$7:$F$26=AF1)*('Daily Schedule'!$G$7:$G$26="FAULT")*('Daily Schedule'!$K$7:$K$26<>0))
Now it is also only looking down to row 26 and when I try and change it to 1000 it shows the Error #Value! again.

The same problem is happening with the formula in the next row as well. This is the formula below:
=SUM(('Daily Schedule'!$F$7:$F$26=AF1)*('Daily Schedule'!$G$7:$G$26="FAULT")*'Daily Schedule'!$AA$7:$AA$26)

I am really not sure what I am doing wrong. I have attached the spreadsheet for you to have a look at.
Question by:SerinaStar
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Select the cell
    press F2
    press Ctrl-Alt-Enter
    LVL 50

    Expert Comment

    Hello SerinaStar,

    the formula is an Array formula and needs to be confirmed with Ctrl-Shift-Enter. Select a cell with the error, hit F2 to edit the formula and then Ctrl-Shift-Enter without changing anything.

    cheers, teylyn

    Author Comment

    Oh ok I see now.
    Is this the right way to calculate this cells or is there a better option. I was hoping that it would just automatically do it.
    LVL 43

    Accepted Solution

    Try replacing SUM with SUMPRODUCT.

    Author Comment

    Excellent I just to tried that and it works well. Thankyou so much for you quick response!!

    Author Closing Comment

    Works excellently....Thankyou so much for you quick response.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now