0

enter image description here

I am trying to get the PRODUCT function work for the certain entries only from a range. In the picture attached, the desired outcome would be to calculate only the entries of column B that have checkmark in correspondong Column C rows.

I tried vlookup, index/match, but to no avail. I guess the problem is that entries are more than one?

Please advise.

Thanks

  • so for this example you are looking for 1.63*2.30*2.10*2.51*1.90 = 37.55? Currently it sounds like you are looking for the equivalent of PRODUCTIF if that function existed. – Forward Ed Oct 3 '18 at 1:29
  • What's the result you want to get? – Lee Oct 3 '18 at 7:39
  • @ForwardEd That’s precisely what I am looking for. – SKYTTEN Oct 3 '18 at 11:00
  • if you use the formula =C4 in an empty cell what answer do you get, and if you use =C3 what answer do you get? I am not sure how formulas will interact with check boxes. – Forward Ed Oct 3 '18 at 11:37
  • @ForwardEd The chekboxes are just a True/False function. so you either get TRUE or FALSE as a return for C column entries. Formulas with checkboxes can be linked as follows: =IF(C3=TRUE, XXX, YYY) – SKYTTEN Oct 3 '18 at 11:47
2

The only way I can currently think of is through the use of a helper column. Others may have better suggestions/answers.

This solution uses a helper column. You can set it up so that your PRODUCT result is either the first entry or the last entry.

Solution at the top of the list.

1) In the first cell use the following formula:

=IF(C3,B3,1)

Basically if the check is there the cell C3 is true and the value from B3 is used. if it is false you do not want it affecting your results, so you use 1 instead.

2) In the next cell use the following formula and copy down as required:

=D3*IF(C4,B4,1)

D3 in this case if the address of your first cell where you placed the formula from step 1). You will multiply the value from the previous cell, by the value for the current row or 1 based on the value in C4. The last cell in the copied list will have the product you are looking for.

Alternatively if you are going the other direction, place the first formula in the last cell in the column. Place the second formula in the second last cell in the column and adjust the references then copy the formula up. The top cell in the column will then have your product.

POC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.