Streamlining a worksheet

Dean UK

New Member
Joined
Jan 4, 2007
Messages
16
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
How about this -
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
Joined
Jan 4, 2007
Messages
16
I tried COUNTIF but I need this to work with filters and wonder if it can be done?
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,173
Messages
5,629,162
Members
416,368
Latest member
PaoloC

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?

Disable AdBlock

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

Disable AdBlock Plus

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
Top