Streamlining a worksheet

[Dean UK]

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?

Thanks in advance
Dean

 

[Gerald Higgins]

How about this -
delete columns G and H, and use three COUNTIFs on Col E.

For example

=countif(E1:E1000,"2")
=countif(E1:E1000,"0")
=countif(E1:E1000,"1")

 

[Dean UK]

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

 

[Gerald Higgins]

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

=d1/c1

Then your COUNTIFs would be

=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]

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