Counting between 2 worksheets where 1 is filtered.

M4rek

New Member
Joined
Jun 17, 2014
Messages
4
I apologise in advance but I have a problemthat I'm really struggling to be able to put into words.


I have a workbook with multiple worksheets in it.

Worksheet 1 (S1) is a 'setup' sheet and contains multiple columns includingCompany, Location, Account Manager, and Regional Manager - then a number of columscontaining tick boxes which put a true or false against particular products wesupply each company.

<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></v:path></v:stroke></v:shapetype><v:shape id="Picture_x0020_1" style="width: 340.8pt; height: 67.8pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:spid="_x0000_i1025"> <v:imagedata o:title="Image1" src="file:///C:\Users\Michael\AppData\Local\Temp\msohtmlclip1\01\clip_image001.jpg"></v:imagedata></v:shape>

Worksheet 2 (Input) has the number of each product supplied to each companyeach month.


Where I'm really struggling is;

I can Filter worksheet 2 by Company, Location, Account Manager, or RegionalManager

When for example I filter by Regional Manager I need a count in worksheet 2 ofhow many companies in this Region we provide a particular product to. Ie. How many ticks are in column K between K5and K104 on Sheet "S1" but only where the Regional Manager is the name chosen in the filter in Sheet "Input".

I can get the total number of companies across All Regions by using theformula: =(COUNTIF('S1'!$K5:$K104,TRUE))-G$114

When the filter is applied I only need to count the number of companies basedon the Regional Manager<o:p></o:p>

<o:p> </o:p>
Any help or guidance would be much appreciated.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,141,129
Messages
5,704,450
Members
421,350
Latest member
jake9951

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