#1
|
|||
|
|||
Countifs Issues
I am having a little issue with formatting the countifs. What I would like is that formula would count that row/person if anyone of the pies had a number in it and dividing it by the total number of people to give me the percentage of people participating.
I could figure out the percentage in a round about way but without the countifs for the person it is all null and void. Thank you in advance for any assistance provided. |
#2
|
|||
|
|||
Where are you proposing to put the count ifs?
is there supposed to be numbers in the clients worksheets? |
#3
|
||||
|
||||
Perhaps
Code:
=COUNTIF($B$5:$G$5;">0")/COUNTA($B$1:$G$1)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
My advice is - start with redesigning it!
With current design, when you get a new location, agent, or type of pie, you have to add a new sheet into workbook, or redesign all location sheets, and then redesign the sheet Data - and all this manually. Instead, use a single sheet for data entry, with columns like Year, Location, Agent, Pie Then you can easily design 2 report sheets, where on one user can select a year a client, and get a list like on nay of your current location sheets, and on another the user can select a year, and got statistics for all Agent for this yer (like your Data sheet). This is all you need to get exactly the same functionality as with current design, an with a lot of less haggle! To get all this more foolproof, you also need sheets, where user can register locations, agents, pies, and optionally a sheet where a list of years which is calculated from some start year up to current one, or the year of workbook is registered. Those registries are used as sources of Data Validation lists in data entry table, and for selecting report conditions. Using data validation also ensures, that user can't easily make any typos when entering new data into data entry table. With such design: Whenever a new location, agent or pie is added, all what is needed is register it. After that, user can enter new data using this new value, and it is displayed in reports (assumed the report sheets are designed correctly); User can use this workbook for several years (in case you chose the option with year list calculated instead of fixed year number. With fixed year number option selected, every new year user creates a new copy of workbook, changes the year number, and clears the entry table leaving at least a single empty data row there (to keep data validations). And this is all - user can start to enter new year's data, |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
count if or countifs how do I do it? | piper7971 | Excel | 6 | 06-11-2015 12:48 PM |
COUNTIFS returning Value Error | ubns | Excel | 1 | 04-16-2015 02:00 PM |
COUNTIFS Help Needed | OTPM | Excel | 2 | 04-09-2014 08:32 AM |
Countifs and Sumproduct | Algo | Excel | 6 | 11-13-2012 07:44 AM |
countifs? | sonyaturpin | Excel | 1 | 05-23-2012 08:29 AM |