I need a sumproduct formula as shown below. The formula will be in a different cell at the bottom of the sheet.
My formula is =IF(SUMPRODUCT(C2:C22="CLF"),SUMPRODUCT(B2:B22/100,BE2:BE22,B2:B22,BE23:BE22))
Basically, If column C contains CLF then divide column B by 100, times the value in column BE (99.95 total), otherwise if column C is blank then column BE times column B (total 9,995.00).
The result of this formula is FALSE, instead of the desired 99.95 or 9,995.00. I have altered this formula every way I can think of, plus researched the net, and I still can't make it work. Any help is greatly appreciated.
Edit: the screen is not showing correctly; Column A shows 99.95 (in red), column B shows 99.95, column C shows CLF, column BE shows 100.
A B C BE
olumn B
My formula is =IF(SUMPRODUCT(C2:C22="CLF"),SUMPRODUCT(B2:B22/100,BE2:BE22,B2:B22,BE23:BE22))
Basically, If column C contains CLF then divide column B by 100, times the value in column BE (99.95 total), otherwise if column C is blank then column BE times column B (total 9,995.00).
The result of this formula is FALSE, instead of the desired 99.95 or 9,995.00. I have altered this formula every way I can think of, plus researched the net, and I still can't make it work. Any help is greatly appreciated.
Edit: the screen is not showing correctly; Column A shows 99.95 (in red), column B shows 99.95, column C shows CLF, column BE shows 100.
A B C BE
olumn B