Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
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
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.
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=C4in an empty cell what answer do you get, and if you use=C3what answer do you get? I am not sure how formulas will interact with check boxes. – Forward Ed Oct 3 '18 at 11:37