• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif and Data Validation.

A

AZExcel

Member
I have a spreadsheet that returns the number of times a nurse(s) performs a particular activity. There are about 5 nurses. The activities as well as the nurse’s name are listed on a sheet called list. named ranges; RN and activities are used.


I use data validation to populate a separate sheet. The activities list about 20 items. And with data validation are selected in a separate sheet in column A and the nurses name in a column B the activities.


The following formula is used to return the number of times the a particular nurse listed on Lists!B2 =COUNTIF(RN,Lists!B2) on a page called reports


This is the question, is there a way to limit the counting of the nurse(s) only if certain activities are listed in column B?. in other words have the formula look at column A and if column B has "xyz", then count but if it has "abc" don't count..


I not sure if this is possible but any help would be appreciated.
 
AZExcel

Have a look at:

http://chandoo.org/wp/2010/02/02/data-validation-using-an-unsorted-column-with-duplicate-entries-as-a-source-list/

This post explains how to setup Sorted Unique lists for Data Validation based on Pivot Tables as an intermediate step
 
Thanks for the reply Excel Ninja however I am not able to find my solution in the link provided. Duplicates are not a problem. below is the list that is populating in column B via Data Validation


Bills

Case Management (Field)

Consultations

Dental

Medical Management

PTR

UR - Compensability

UR - Concurrent

UR - Precertification

UR - Utilization Review


Meetings

TOP

Log Maintenance


Column A Column B

Jane Bills

Debi Case Management( Field)

Jane Dental

Sally Bills

Jane TOP


using Jane as an example the countif formula would normally return 3. But I only want to count Jane if column B contains any value in the list except for the last 3 items in the list. So the answer I am looking to return for Jane is 2
 
AZExcel

As I suggested setup a pivot table which will have names down one side and Activity accross the top

Manually unselect the Activity Field values that you don't want to see

Now you can use an Offset/Match to either return all the fields that Jane has or the Total "2" at the end of that row, depends on your use.


Can you post your file somewhere ?
 
Ninja:


Thanks for the reply, and for explaining the solution.. I didn't understand what you meant initially.... I will work with it and see if this works. I am not sure how to post a file in this forum, but can post it to my Windows Live Sky Drive but I will try your solution 1st..Thanks again!!
 
If you post it to Skydrive make sure there is no confidential or personal data in it
 
ok... what you are looking for is essentially a multi-condition count. you can use SUMPRODUCT() formula.


Assumes nurses are in A1:A20 and their jobs are in B1:B20 and the nurse name in E1 and full list of activities in the named range "activities"


Code: 
=SUMPRODUCT((A1:A20=E1)*(MATCH($B$1:$B$20,activities,0)<11))


The <11 part ensures that activities listed in B1:B20 are the first 11 activities in the master list (there are total of 14 as per your pasted data). Adjust the number to whatever you fancy.
 
Key Master Indeed!!


That is exactly what I needed. I was actually trying SUMPRODUCT as part of the solution but could not get the total result. The Match and < were the missing pieces..


How was that you were able to put that together? I will study the components of the formula to understand it better


Thank you very much !!!!
 
You must log in or register to reply here.
Back
Top