Help to prevent #N/A error in formula

[costgeek]

I got some great help a few days ago to develop a formula that mixed SUMPRODUCT and LOOKUP functions. The formula works great when the referenced areas are all populated, but if a row is blank, I get the #N/A error. I have attached a spreadsheet that has a better explanation.

Bottom line is that I need the formula to work even when there are blank rows in the model.

Any help would be appreciated!

[JohnTopley]

=IFERROR(your formula,"")

=IFERROR(SUMPRODUCT(K5:K8*LOOKUP($J$5:$J$8,$B$4:$C$9)),"")

[costgeek]

Thanks but I still need the formula to calculate rows that are populated.

[ChemistB]

There might be a simplier formula but off the top of my head, use this ARRAYED formula in K9

=SUMPRODUCT(K5:K8*IF(ISERROR(LOOKUP($J$5:$J$8,$B$4:$C$9)),0,LOOKUP($J$5:$J$8,$B$4:$C$9)))

If the empty value is always in the last row(s) then you could use dynamic named ranges and stick with your original formula (except with the named ranges). Is that how it is?

[costgeek]

It's a cost model so deleting data from a row in the middle will definitely happen. So while there will be empty rows at the end as the model grows, there will still be times when the missing data is not at the end. Giving your solution a try now. Fingers crossed.

[costgeek]

That one's making the answer zero all the time...even if I populate all the rows.

[ThirdFret]

The following is an ARRAY formula solution ***************

=SUMPRODUCT(K5:K8*IFERROR(LOOKUP($J$5:$J$8,$B$4:$C$9),1))

Copy the formula into cell K9, click in the formula bar at the top (as if you were about to edit the formula), and press Ctl+Shift+Enter

[ChemistB]

As an array formula, are you entering it
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[costgeek]

You know how you skim past instructions because you think you already know what they say? Yeah, I did that

It worked!

I consider myself pretty good at excel but this is the first I've heard of "array formulas". I guess I need to learn about those.

THANK YOU