Forum Discussion
blank form, count if cell is not blank and is = to a cell
Take a look at this. See if it does what you're desiring. Try adding a name or two, repeating them. You'll see that the summary report on the right grows with each added name, and keeps track of the total count for each name. This solution does use the Dynamic Array functions FILTER and UNIQUE and SORT (all put together in one formula)...so if you don't have the most recent Excel, it likely won't work. In which case, I'd recommend getting it (via subscription to Microsoft 365).
Now, to avoid problems with spelling, etc., you might want to use data validation so that there's a drop down list of acceptable names for each location. That's another topic.
actually, we need to count on providers and not count if provider is blank and not have it accumulate in "blank" counts
If that was meant as feedback based on what I gave you, it isn't clear what you're saying. So far as I can tell, it does give you a count of providers and not blanks.
It would also be helpful if you answered the questions I asked. Like what version of Excel are you using? It's possible that you're using something other than the most recent version, in which case your feedback may be caused by that--you're not seeing what you'd be seeing if you were using the most current version.
This is what it looks like with a current version of Excel. If that's not what you're seeing, let me know. The summary report area will grow as additional Providers add their rows, but it will always show any one provider's name but once, with a count of how many patients that particular provider saw.
hi,
2010 is the version of excel I am using. When using your excel file and I change or add a name in the provider section the summary all change to
#NAME?
I hope that the attached works with Excel 2010. I have no way of knowing for sure, but I think it should.
This still incorporates a different way of summarizing the count of patients seen by each provider, in that I have created--in the example--a summary at the top of the page. This summary could easily be moved to the bottom (so that it prints on a second page or reverse side of the first page), or the summaries could be moved as a whole to a page consisting solely of summaries for each location.
I've made an assumption, possibly invalid, that the personnel assignments at each location are relatively stable, such that the list of names in the summary section is stable, and can be used as the basis for both a drop down in that column of the record keeping "ledger" AND as the basis for the COUNTIF formula that gives you the count of patients seen by each provider.
One of the advantages--which you've not been able to see--of the newer versions of Excel in this particular type of instance (on display in the previous sheet I uploaded), is that the provider list in that summary report is derived from the entries in the ledger, with UNIQUE being used to show any name only once, regardless of how many times the Provider in question actually provided service that day, and that therefore any given provider could move from location to location and the various location specific trackers could still function without interference from a "network" or "systems administrator."