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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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")``````

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

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``

Then your COUNTIFs would be
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.

I think COUNTIF will include rows that you have filtered out.

Replies
5
Views
200
Replies
5
Views
273
Replies
2
Views
173
Replies
9
Views
270
Replies
2
Views
524

1,217,388
Messages
6,136,298
Members
450,002
Latest member
bybynhoc

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.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

Follow these easy steps to disable AdBlock Plus

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