I have a spreadsheet with 3 sets of 2 columns and endless rows. The first column will be one of 3 letter codes. The second column will be a number expressed in whole numbers and digits following a full stop, such as xx.xx. Depending on the letter code in the first of each set of columns, a fraction is applied to the number in that set and a sum of those (in decimal form) will be displayed in the cell with this formula. As an example:
Column A has code "X" input, Column B has number 12.34, Column C has code "Y" input, Column D has number 56.78, Column E has code "X" input, and Column F has number 9.01 (for brevity, I'll just use 3 sets of columns instead of 5).
"X" means that its associated number will be multiplied by 0.2 and adds 0.1 to it
"Y" means that its number will be multiplied by 0.4 and adds 0.15 to it
"Z" means its number will be multiplied by 0.6 and adds 0.25 to it
The Sum cell, in this example, will total (0.2x12.34=2.468+0.1=2.568) + (0.4x56.78=22.712+0.15=22.832) + (0.2x9.01=1.802+0.1=1.902) = 27.302
SUMIFS can check the logic, but it can't apply the varying fractional components of the equation. IF(SUMIFS) might be able to, but I have been unable to get the syntax correct because if a null is returned, what I came up with just applies the fractioning formula. Finally, I want to ignore any other codes besides these three specifically. Here's the actual formula I came up with:
=SUM(SUMIF(E4:N4,"CC",F4:O4)*0.026+0.1,(SUMIF(E4:N4,"NP",F4:O4)*0.029+0.35),(SUMIF(E4:N4,"PP",F4:O4)*0.03+0.15))
Any help would be appreciated.
