*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...

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,

=IF(COUNTIF(E2,"*16*")>0,

=IF(COUNTIF(E2,"*Pineapple*")>

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!

Here's a sample sheet/form:

https://docs.google.com/

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