Microsoft Office Forums Countifs Issues
 Register FAQ Search Today's Posts Mark Forums Read

#1
10-25-2021, 11:15 AM
 bbutl027 Windows 10 Office 2016 Novice Join Date: Mar 2015 Posts: 17
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.
Attached Files
 Sample.xlsx (28.0 KB, 16 views)
#2
12-05-2021, 01:13 AM
 Purfleet Windows 10 Office 2019 Expert Join Date: Jun 2020 Location: Essex Posts: 345

Where are you proposing to put the count ifs?

is there supposed to be numbers in the clients worksheets?
#3
12-05-2021, 01:44 AM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,570

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
12-06-2021, 12:06 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 696

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,

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post piper7971 Excel 6 06-11-2015 12:48 PM ubns Excel 1 04-16-2015 02:00 PM OTPM Excel 2 04-09-2014 08:32 AM Algo Excel 6 11-13-2012 07:44 AM sonyaturpin Excel 1 05-23-2012 08:29 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:58 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top