Count if query

[burdie87]

Hey guys and girls,

Hopefully a quick one for all you wizz kids on excel.

Is it possible to make an if statement in a cell that only adds the cells in a collum if they are not struck through.

I use this for when an order is complete and would like my total at the bottom of the column to change when I strike out an order in the list.

Any ideas will be appreciated.

Regards

Alan.

 

[Norie]

Alan

Why not just have another column indicating if an order is complete?

Then you could use a straightforward SUMIF/COUNTIF.

And if you still want the strike through that can easily be achieved through conditional formatting.

 

[burdie87]

Norie,

Thanks for your thought. I was going to do this but there are other columns that would be effected as well as the fact I wanted a formula that would work for multiple columns which Kris's does.

The last step is to simple find a way of making the page refresh without having to double click the formula cell and click the green tick to make the figures update.

Alan.

 

[Norie]

Alan

And you couldn't use more than one formula?

And utilise some of Excel 256 columns?

 

[burdie87]

Well I could but its to do with print per page & layout. Also im so close now with this formula it seems a shame to throw it away to then go back to conditional formatting and extra columns.

Alan.

 

[Krishnakumar]

Hi,

Use Format Painter to get the figures Strikethrough or hit F9.

HTH

 

[burdie87]

Kris,

What is format painter?? also when I hit F9 nothing changes.

Alan.

 

[Krishnakumar]

Hi Alan,

An UDF won't fire when a cell format changes. So you have to force the workbook to calculate the cells. (F9 or Shift+F9)

Format Painter is a command button to copy the format of a cell.(see the icon something like a brush on your toolbar)