Help with SUMPRODUCT formula

[SloopJohnB]

I am having issues with the SUMPRODUCT formula in the attachment spreadsheet.

I want to get the totals of pipe, Elbows, Tee & Valves in the blue cells by the number off Droppers (yellow cells) by the amount of the material to be populated in the red cells.

The formulas in the blue cells don't work for the total elbows, tee & valves.

Thank you in advance.

[Richard Buttrey]

Hi,

That's because you don't have any Quantities on row 7 in cells DD:DF, DL:DN etc.. If you want to use array formulae like sumproduct then each cell has to have a value. If you enter 26 in DD7 for instance you'll get an answer in CZ10

[SloopJohnB]

Thanks I have unmerged the cells in row 7 and entered the same quantities in the 4 cells DC:DF, DG:Dj etc.

So the formula doesn't work with merged cells.

[Fotis1991]

As a general rule you have to avoid merged cells.

These give you only problems!

[oeldere]

@SloopJohnB

Please Login or Register  to view this content.

Advice:

Don't work with merged cells, you get in trouble with it sooner or later.

In your case (as you already noticed) sooner.

[newdoverman]

Using the suggestions that were given to you previously and slightly changing the formula so that it can be copied across and down without manually changing the formula I have made the changes to your sample workbook to give you a visual of what has been suggested.

[SloopJohnB]

Thanks for the information.....no merged cells from now on.

[newdoverman]

Merged cells can up and "bite" at the most inopportune of times. If you feel that you must have the visual affect of merged cells, try selecting the cells, right click and choose format cells, Alignment Tab, select Center Across Selection.