Countif totals

[DIM TIM]

I'm an excel noob so please forgive me if the answer to this is actually screamingly obvious.

I have a worksheet where I enter daily sales of different products on seperate rows.At the end of each row is a column with the total sales figures.So far so easy but after that I want a column with the quantities sold and am trying to figure out how to do this.All I really need is to enter a formula into the cells in that column which gives the number of values excluding zeros which have been entered into the cells on each row.

[Ron Coderre]

Perhaps this...

A1: Product Name
B1:Z1 (various numbers)

This formula returns the count of numbers greater than zero in B1:Z1
AB1: =COUNTIF(B1:Z1,">0")

Does that help?

[DIM TIM]

Thanks, yes that certainly did help, all I've got to do now is figure out why. One further question. I've set the worksheet to accomodate what I expect the maximum sales to be, if I end up having to expand it by adding columns to accomodate increased sales can I make the formula update automatically or will I have to redo it each time?

[Ron Coderre]

There are several approaches you can use. Here are some options:

1) Put the formula to the LEFT of the data and extend it's referenced range to column IV.

2) Change the formula so it references a blank column to the right of the data. With data from B1:Z1, put the formula in AB1 and reference B1:AA1.
AB1: =COUNTIF(B1:AA1,">0")
If you need to insert more columns, put them BEFORE Col_AA.

3) Reference a Dynamic Range Name in the formula. DRN's automatically expand and contract to accomodate the amount data.
Debra Dalgleish's website has instructions:
http://www.contextures.com/xlNames01.html#Dynamic

Other approaches are more complicated and some of them involve volatile functions, which can make your workbook sluggish.

Does that help.