Excel formula help

There are two things I want to do with Excel:

  1. I have three columns. I want the third one to show a product of the other two. I can do this individually, but I want it to continue for each cell as I enter them in. I get new results to put in every few days, and I don’t want to have to keep doing the paste formula thing. This seems to have happened on its own after much mucking about, but I’d like to know how to duplicate it.

  2. Say I have a fourth column, with one of two words in it. If I wat to keep a seperate running tally of the SUM of all of the products in that third column for each of the two words in the fourth column… just put the number somewhere… how can I do this?

Thanks much.

For the first example, we’ll assume the first numbers are in column A and the second are in B and that the first row you use is row 1. We’ll put the answer in column C. Type the following formula in row 1 of column C:

=IF(SUM(A1:B1)>0,SUM(A1:B1),"")

Then copy and paste this formula as far down as you think your spreadsheet might grow. The IF function is to eliminate those nasty zeros that show up when A and B are blank.

For the second question, we’ll assume that you want to count the word “WORD” and the list of words is in column D, from row 1 to row 200. Paste the following formula into the cell that you would like to show the result in:

=COUNTIF(D1:D200, “WORD”)

You may adjust these to fit your needs, of course.

I see what your COUNTIF thing does, I see how that works…

but how do I make it so that in addition to counting if it’s “word” summing the column (which is F)

something like:

=SUM(F3:F200) (COUNTIF C1:C200, ‘WORD’)

but the two won’t acknowledge each other…

the first one, it makes each cell a product (once I changed it to PRODUCT) of A1 and B1 and not the A and B of that row… how do I make something the product of the a ab cell in that row?

To sum the values in column C where columd D is equal to “WORD”, use the following:

=SUMIF(D1:D200,“WORD”,C1:C200)

As for the problem you’re having with the first formula I gave you, it sounds like you have absolute positioning in the formula before copying. Look at the sum formula in C1 and remove any dollar signs that are visible before copying.

Rory,

I faced a situation similar to the first you describe. I was trying to create a column of averages for an ongoing series of scores. The problem was that if I had 10 scores, and the eleventh column was the average of all of them, when I received a twelfth score, I needed to Insert the column and change the formula.

I finally realized that I could just make the first column my average and write the formula (using an IF statement) to include columns way the hell out to the right.

Hope that helps.

Rory,
I think this is what you are trying to do and here’s how I did it.[ul]
[li]In A1:A10 & B1:B10, I entered random numbers.[/li][li]In cell C1, I enter the following formula: =IF(PRODUCT(A1:B1)<>0,PRODUCT(A1:B1),"").[/li][li]Then grabbed the lower right corner to copy it down to the bottom of the screen.[/li][li]In D1:D10, I enter the words “blue” or “green”, randomly.[/li][li]In F1, I enter the word “blue” & in F2 enter “green”.[/li][li]In G1, I entered the following formula: =SUMIF(D1:D8,F1,C1:C8).[/li][li]Then grabbed the corner to copy it into G2.[/li][/ul]

Hope that helps,
Jim

DMC’s last formula was quite right, thanks guys.