Sum Product taking account of zeros

cell is zero, then the SumProduct ignores that cell in the daughter sheet (NormData). The parent cells are in the sheet Raw Data; the daughter cells in NormData.

I would appreciate help in adapting the SumProduct function to take account of when the parent cell is zero. Example spreadsheet ?Improving SumProduct Analysis? attached.

At present, I generate a figure from derived cells in sheet NormData (derived from Raw Data) using SumProduct such as =SUMPRODUCT($J17:$NT17,$J$30:$NT$30) in cell C17 to G17 of NormData and have used it in col.B too except for the example cell B17. B17 has my failed attempt to do what I want. These cells if Cols B ? G (except B17) work fine, but doesn?t take account of when the parent cell is zero ? that is, that item is not actioned in my lifestyle.

To be explicit:, I don?t want to use any of the cells in the Rows (such as Row17) of NormData where the original data in Raw Data Row17 is zero.

I?ve tried using =SUMPRODUCT($J17*'Raw Data'!J17/'Raw Data'!J17:$NT$32*'Raw Data'!NT17/'Raw Data'!NT$32,$J$29:$NT$29) in NormData B17, but that fails. Note I divided by Raw Data J32 in an attempt to keep figures similar to the original SumProduct. However, this doesn?t work.

Is there a solution please?

If you ignore the zeros or not that does not matter in a SUMPRODUCT.
The difference between the 2 sheets arises because the data is not the same.

Thanks for trying to help HansDouwe.

Yes, I know the two sheets are different. I generate the NormData from the Raw Data sheet.

I essentially wish to do a SumProduct in the NormData sheet, but where the corresponding cell in the Raw Data is zero - for example P17, then I do not want the SumProduct for that cell to be included in the total SumProduct generated.

Apologies. I have not explained my problem clearly enough.

The generated cell values in the NormData sheet are such that even when the Raw Data data is zero, there is a non-zero value - by virtue of how the NormData cell values are calculated: ((Raw Data cell value) - (Average for that column))/StdDev for that column. Hence, when I do a SumProduct for that Row and the col. constants (index values) in NormData row 32, such zero Raw Data cells will cause a value to be used in the SumProduct.

However, I do not want any Product of the NormData and col. constants to be part of the SumProduct.

For example, Raw Data cell P16 is 0. But NormData cell P16 is -0.5256 - the latter is used in SumProduct (=SUMPRODUCT($J16:$NT16,$J$29:$NT$29)) to generate the value in B16. I wish to not include that NormData cell P16 in the SumProduct. Is this possible somehow?

This formula on sheet NormData in row 17 excludes the parts in SUMPRODUCT where in the sheet Raw Data in row 17 is a zero in the same place:

Formula:

Please Login or Register  to view this content.

That works exactly as I needed. Many thanks HansDouwe.

I've never used the Filter function before. I must get familiar with it.

Your help is much appreciated.

Thanks. I had forgotten to update.

You are welcome. Thanks for the feedback. I'm glad to help you.

Please consider adding reputation to the answers of any helpers if you think the answer has earned it?
This can be done by clicking on * Add reputation at the bottom left of the answer.