need to identify if missing input

[RH6978]

I am working on a simple sheet to allow people to count inventory quicker by allowing them to count either cars or spaces for a large parking lot - there's over 9,000 available stalls at this location. The way this works is that the individual will count whichever is easier - if there's a lot of cars in a certain row, they'll be able to more quickly count empty spaces, and vice-versa. I've created two columns - one for either number acquired. My trouble arose when I tried to total everything up at the end.

As you can see in the sample attached, I have used an IF statement in the column B to return "error" if there is missing data in column C. The blank sheet will have this IF statement running all the way to the last row (highlighted blue in the example). The idea is that if they count cars, they will key in the number, which will replace the formula since it's not needed. Otherwise it will calculate the number of cars by subtracting spaces from available stalls. My trouble comes in when totaling up at the end. If every row has either cars or spaces entered, then I get a correct total, but if there is any missing data the SUM function simply skips it and totals up what numbers it does have.

I need the sum total cell (highlighted yellow in the example) to return some error message to let the user know they missed keying something in. How do I achieve this?

Thanks in advance!

[Limor_OP]

So there might be cases when cells can be left blank and it is correct that way, and some cases where it is blank because it was forgotten?
how can you tell when it's fine and when error?
column B is manually entered or a formula?

[RH6978]

So there might be cases when cells can be left blank and it is correct that way, and some cases where it is blank because it was forgotten?
how can you tell when it's fine and when error?
column B is manually entered or a formula?

First the latter question: I have it set at the beginning as a formula in column B. If the person counting inventory counted CARS instead of SPACES, they would type that into column B. In the sample I provided, the person counted 4 cars in Row #1, so they typed 4 over the formula in column B. In Row #2, however, they counted 5 spaces, so they typed that into column C, and the formula calculated there were 10 cars in that row. In Row #3, nothing has been input, so the formula is telling you you're missing data.
As to the former question: yes, column C would only have data keyed into it if the person counted spaces instead of cars for that particular row, like Row #1, 5, and 6 are in the sample provided.

[Limor_OP]

Hi
Thanks for the explanation.

You can either create a real error, and not just the expression by using this, and then the total will also return an error #NUM

In B4:
=IF(ISBLANK(C4),N/A,D4-C4)

Or you can use in C9 to return the word ERROR:
=IF(COUNTIF(B1:B400,"error"),"ERROR", SUM(B2:B7))

[RH6978]

That works beautifully. Thank you! I knew there had to be a function that would achieve what I needed, and I knew it wasn't just the SUM function.

[Limor_OP]

You're welcome : )