yogi_Set Up Computed Columns For Row By Row Computations In A Form And Also A Grand Total

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #654   Jul 24, 2012     www.energyefficientbuild.com.


user Amber0803 said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/KEKsqI3M9kY)
Last row does not populate calculations after form submission, countif formula
Hi All,
I know this has been addressed before but I can't seem to find an answer that fits my needs, that I can understand anyway.
I have a form that basically calculates commission on certain items, they are set up as checkboxes so all of the items that are checked
go into the same cell. To calculate how much each is worth I have a column for each item and I use a COUNTIF formula to search that one cell (the E column) for
specific text. My problem is that for each form submission, the formulas don't copy down. I know I need to use an array formula but I can't get it to
work. I've also read that I can't use a COUNTIF formula in an array and that's where I get completely lost...
I have formulas in columns G:M that I need to repeat
These are a few examples of the formulas I am currently using:
=IF(COUNTIF(E2,"*Super*")>0,COUNTIF(E2,"*Super*")*4,"")
=IF(COUNTIF(E2,"*16*")>0,COUNTIF(E2,"*16*")*2,"")
=IF(COUNTIF(E2,"*Pineapple*")>0,COUNTIF(E2,"*Pineapple*")*1,""
Hoping someone out there can help me, I've been banging my head against my desk for hours...
----
Hi Yogi,
Here's a sample sheet/form:
https://docs.google.com/spreadsheet/ccc?key=0AkzKlHQOFyfDdDZIUWJXV3NYR05iVzhvM3M3NW45S1E
The form populates columns A-F, then the rest of the columns are calculations.
The formulas for the calculations are correct in the first two rows (which had to be manually put in after a form submission) but when a new submission is entered, like the last row,
the formulas do not appear. Each calculation column searches column E for a certain criteria then multiplies it by the correct number. At the end, the bonus column adds 1 if columns
H and I are on a single order. And then the total adds the entire row.  
Thanks in advance for your help!
---------------------------------------------------------------------------------------------
following is a solution to the problem