Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-25-2021, 11:15 AM
bbutl027 bbutl027 is offline Countifs Issues Windows 10 Countifs Issues Office 2016
Novice
Countifs Issues
 
Join Date: Mar 2015
Posts: 17
bbutl027 is on a distinguished road
Default 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
File Type: xlsx Sample.xlsx (28.0 KB, 21 views)
Reply With Quote
  #2  
Old 12-05-2021, 01:13 AM
Purfleet Purfleet is offline Countifs Issues Windows 10 Countifs Issues Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Where are you proposing to put the count ifs?

is there supposed to be numbers in the clients worksheets?
Reply With Quote
  #3  
Old 12-05-2021, 01:44 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Countifs Issues Windows 7 64bit Countifs Issues Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #4  
Old 12-06-2021, 12:06 AM
ArviLaanemets ArviLaanemets is offline Countifs Issues Windows 8 Countifs Issues Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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,
Reply With Quote
Reply

Thread Tools
Display Modes


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 Issues 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 Issues Countifs and Sumproduct Algo Excel 6 11-13-2012 07:44 AM
countifs? sonyaturpin Excel 1 05-23-2012 08:29 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:55 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft