Forum Discussion
blank form, count if cell is not blank and is = to a cell
Hi,
I have created a blank form to be filled out by my clinic workers. and as they fill in the Provider field I need it to count all the Provider fields (ie: d3:d44) that are in d3, d4, d5 as we go down the rows, something like this, but this does not work properly....
=SUM(IF(D3:D44=D3,1,0)) this is e3
=SUM(IF(D3:D44=D4,1,0)) this is e4
=SUM(IF(D3:D44=D5,1,0)) this is d5
and ...
any suggestions?
sb
9 Replies
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.
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.
I'm looking at your form and having a difficult time visualizing what should be happening. Could you describe what you're expecting a clinic worker to fill in here (perhaps even filling out one of the forms)...and what then should be appearing in column E?
It looks as if you are wanting the count of patients seen by each provider, but I'm not sure. Nor am I sure that this would be the way to do it. But let's start with what the goal is.
Can I also ask what version of Excel you're using? There are some newly available dynamic array functions that might be the solution.
And are you wedded to this format? Might it be acceptable to have the number of patients seen by each provider at each location appear off to the side of this input form?
