Creating Excel Formula

Copper Contributor

Hello!

 

I am currently working with an excel spreadsheet that providers at my job use to document the number of patients who sign up with or decline using a mobile health application. In one column (let's say Column A), the provider is asked to select their name from a drop-down list (the drop-down list is pre-populated in each cell in that column). Then, in Column B, the provider indicates the date a patient signed up with the application. Providers have also been given the option to input the date a patient declined downloading the app in Column C. Each row of the spreadsheet deals with a unique patient interaction which will result in a response to either Column B or Column C.

 

Ideally, I would like to be able to determine the unique number of patients who have either signed up with or declined using the app by provider name (the spreadsheet is empty so this would have to be automated). I have previously created a COUNTIFS formula that lets me know the number of patients who signed up/declined the app based on a range of dates for a specific time period (e.g., July Week 1, July Week 2). The formula roughly translated to something like this: =COUNTIFS(range,">=DATE",range,"<=DATE")

 

This worked out perfectly fine! As I input different dates in either COLUMN B or C on the master Excel sheet, it automatically generates the total numbers in a table within another Excel sheet that is separated by the weeks of the month. My problem is, that I don't know how to do the same thing by the provider.

 

Can anyone help me? I have tried many different formulations, but I can't seem to get the right one.

 

Your help would be greatly appreciated!

 

Omar

2 Replies

This is something that can easily be done using a pivot table.

Hi Jan,

 

I tried doing this with a pivot table, but it did not seem to work. Perhaps because I used a drop-down list in the provider column and dates versus numbers for the sign-ups and declines? I have attached a de-identified spreadsheet as a reference.

 

My boss wants me to create a spreadsheet that providers will use to track patients who either sign up or decline using a mobile health app. She then wants the data to automatically populate in other sheets with a corresponding graphic (pie chart, bar graph). I was able to successfully do this with the reasons for the decline, patient demographics, and weekly enrollment. The trouble I am running into is with weekly enrollment by provider name.

 

In creating a pivot table, their names appear as "blank." I tried to fill in some of the data, but it did not appear in the pivot table. Is there perhaps a formula I could use that would auto-populate the data in a separate sheet with a corresponding graphic? I attempted to use a MATCH and COUNTIFS function, but that counted the provider's name along with the patient who signed up/declined.

 

Your help will be greatly appreciated!

 

Best,

Omar

 

@Jan Karel Pieterse