How do I exclude certain cells from a sum product formula in Excel?

[ardask]

Using sum product I calculate weighted average of rate of amounts I have, as per below case I would like to exclude highlighted amount and rate from the formula. Any quick solutions?
Thanks
Arda

 

[jasonb75]

You could use an extra column as a control by entering 1 (include) or 0 (exclude) in C2:C17, then change your formula to

=SUMPRODUCT(A2:A17,B2:B17,C2:C17)/SUMPRODUCT(A2:A17,C2:C17)

 

[ardask]

You could use an extra column as a control by entering 1 (include) or 0 (exclude) in C2:C17, then change your formula to

=SUMPRODUCT(A2:A17,B2:B17,C2:C17)/SUMPRODUCT(A2:A17,C2:C17)

Thanks Jason, adding an extra column will not really help me here as the picture I sent was only a small part of a bigger file (as below). Excluding and including will depend on the cell I take as a reference and it will not be always the same whether I exclude or not. Any solutions coming to your mind without adding an extra column?

 

[jasonb75]

If you want to exclude all rows that are equal to a single reference cell then you could use something like.

=SUMPRODUCT(A2:A17,B2:B17,--(A2:A17<>A4))/SUMPRODUCT(A2:A17,--(A2:A17<>A4))

Formulas like this are often unique to the requirement so we would need to know the nuances of the criteria to be come up with anything more definitive.
For example do you want to exclude any amount with a specific rate? Any rate with a specific amount? A specific rate with a specific amount? Multiple rates and / or amounts?

 

[Fluff]

Cross posted How do I exclude certain cells from a sum product formula in Excel?

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.