# Streamlining a worksheet

#### Dean UK

##### New Member
Hi

I’m hoping someone can help me.

I’m trying to reduce the number of columns needed to tally up the totals of 3 possible outcomes

I have 8 Columns; Column A= Item Code; B= Category; C=Target figure; D=Actual figure; E=Outcome Code; F=Items scoring 0; G=Items scoring 1; H=Items scoring 2

I want to be able to filter categories in Column B.

Column C contains target figures. Each is different.

Column D contains the actual total.

Column E – If the actual total is greater than the target figure the figure 2 appears here
If the actual total is less than 90% of the target figure the figure 0 appears here.
If the actual total is 90% or more but less than 100% of the target figure the figure 1 appears here.

Column F – If there is a 0 in Column E, a 1 appears here

=if (E5=”0”, “1”,””)

Column G – If there is a 1 in Column E, a 1 appears here

=if (E5=”1”, “1”,””)

Column H – If there is a 2 in Column E, a 1 appears here

=if (E5=”2”, “1”,””)

At present I have these formulas at the top of F, G and H respectively

=subtotal (2, F5:F19)

= subtotal (2, G5:F19)

= subtotal (2, H5:F19)

However I want to avoid the need for Columns G and H if possible as they make the spreadsheet look clumsy and untidy. Can you suggest a way of cutting down on these 2 columns but still giving me the same results for items scoring 0, 1 and 2?

Dean

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Gerald Higgins

##### Well-known Member
delete columns G and H, and use three COUNTIFs on Col E.

For example
Code:
``````=countif(E1:E1000,"2")
=countif(E1:E1000,"0")
=countif(E1:E1000,"1")``````

#### Dean UK

##### New Member
I tried COUNTIF but I need this to work with filters and wonder if it can be done?

#### Gerald Higgins

##### Well-known Member
Sorry, should have said, delete Col F as well.

An even better way would be to replace your Col E with a formula which, instead of simply flagging up the variance from target, actually shows you the variance percentage, such as
Code:
``=d1/c1``

Code:
``````=countif(E1:E1000,">1")
=countif(E1:E1000,"<0.9")
=count(E1:E1000)-A1-B1``````
which assumes you have inseerted the first two COUNTIFs in cells A1 and B1.

#### Gerald Higgins

##### Well-known Member
I think COUNTIF will include rows that you have filtered out. Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,626
Messages
5,838,431
Members
430,547
Latest member
jopshio ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back