Creating Excel Formula

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

JKPieterse