I am not a pro at Excel but I did try researching this for some time. This is an example. In column E I have 12,000 items that are duplicates but I want to add them up to show totals of how many times this provider referred a patient. I clicked on the cell I want the data to be placed. Next, Iclicked Data, → Consilidate → Reference Range (I think this is where I am making an error) but I choose all colum E and "add". Last I click "OK" and nothing happens. Visit DateVisit ProviderVisit NumberAppt TypeReferring Provider1/4/2021John Doe417567NP - New patient1 - Allen, Robert D1/6/2021John Doe418290NP - New patient1 - Allen, Robert D3/17/2021John Doe425336NP - New patient1 - Allen, Robert D3/17/2021John Doe425336NP - New patient1 - Allen, Robert D4/21/2021John Doe429804NP - New patient1 - Allen, Robert D6/2/2021John Doe435235NP - New patient1 - Allen, Robert D10/13/2021John Doe453178NP - New patient1 - Allen, Robert D10/13/2021John Doe453204NP - New patient1 - Allen, Robert D4/21/2021Michaels Duncan429804NP - New patient1 - Allen, Robert D6/2/2021Michaels Duncan435235NP - New patient1 - Allen, Robert D10/13/2021Michaels Duncan453178NP - New patient100 - Sasaki, Elizabeth M10/13/2021Michaels Duncan453204NP - New patient101 - Sasaki, Elizabeth M2/8/2021Michaels Duncan421849NP - New patient102 - Sasaki, Elizabeth M10/25/2021Michaels Duncan454345NP - New patient103 - Sasaki, Elizabeth M12/7/2021Michaels Duncan460732NP - New patient104 - Sasaki, Elizabeth M10/25/2021Michaels Duncan454345NP - New patient105 - Sasaki, Elizabeth M12/7/2021Kim Jones460732NP - New patient106 - Sasaki, Elizabeth M9/2/2021Kim Jones447316NP - New patient1099 - Narayan, Anand9/17/2021Kim Jones448959NP - New patient1100 - Narayan, Anand10/20/2021Kim Jones453735NP - New patient1101 - Narayan, Anand9/2/2021Kim Jones447316NP - New patient1102 - Narayan, Anand9/17/2021Kim Jones448959NP - New patient1103 - Narayan, Anand10/20/2021Kim Jones453735NP - New patient1104 - Narayan, Anand3/29/2021Kim Jones427875NP - New patient1105 - Narayan, Anand7/22/2021CRINE - Mike442258NP - New patient1106 - Narayan, Anand8/9/2021CRINE - Mike444456NP - New patient1107 - Narayan, Anand8/13/2021CRINE - Mike444547NP - New patient100 - Sasaki, Elizabeth M

SUZETTE1971 =IF(ROW(F2)=LARGE(IF($F$2:$F$28=F2,ROW($F$2:$F$28)-1),1)+1,COUNTIF($F$2:$F$28,F2),"")With the help of Riny_van_Eekelen 's solution an alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

hello, I'm not sure if I misunderstood, Use function--sumif Should be able to complete

SUZETTE1971 Here is a simplified suggested solution with the formula =SUBRESULT. You must first insert the data into a spreadsheet. Sample file is included. Hope I was able to help you with this info. NikolinoDEI know I don't know anything (Socrates)

It doesn't seem to be what he needs

NikolinoDE I don't want to add up all referring providers at once but with 12,000 rows of information in colum "E" Referring Providers, I want it to add up like this. Can you help me please?

SUZETTE1971

Copper Contributor

Jan 13, 2023

Consolidating Data

I am not a pro at Excel but I did try researching this for some time. This is an example. In column E I have 12,000 items that are duplicates but I want to add them up to show totals of how many times this provider referred a patient. I clicked on the cell I want the data to be placed. Next, Iclicked Data, → Consilidate → Reference Range (I think this is where I am making an error) but I choose all colum E and "add". Last I click "OK" and nothing happens.

Visit Date	Visit Provider	Visit Number	Appt Type	Referring Provider
1/4/2021	John Doe	417567	NP - New patient	1 - Allen, Robert D
1/6/2021	John Doe	418290	NP - New patient	1 - Allen, Robert D
3/17/2021	John Doe	425336	NP - New patient	1 - Allen, Robert D
3/17/2021	John Doe	425336	NP - New patient	1 - Allen, Robert D
4/21/2021	John Doe	429804	NP - New patient	1 - Allen, Robert D
6/2/2021	John Doe	435235	NP - New patient	1 - Allen, Robert D
10/13/2021	John Doe	453178	NP - New patient	1 - Allen, Robert D
10/13/2021	John Doe	453204	NP - New patient	1 - Allen, Robert D
4/21/2021	Michaels Duncan	429804	NP - New patient	1 - Allen, Robert D
6/2/2021	Michaels Duncan	435235	NP - New patient	1 - Allen, Robert D
10/13/2021	Michaels Duncan	453178	NP - New patient	100 - Sasaki, Elizabeth M
10/13/2021	Michaels Duncan	453204	NP - New patient	101 - Sasaki, Elizabeth M
2/8/2021	Michaels Duncan	421849	NP - New patient	102 - Sasaki, Elizabeth M
10/25/2021	Michaels Duncan	454345	NP - New patient	103 - Sasaki, Elizabeth M
12/7/2021	Michaels Duncan	460732	NP - New patient	104 - Sasaki, Elizabeth M
10/25/2021	Michaels Duncan	454345	NP - New patient	105 - Sasaki, Elizabeth M
12/7/2021	Kim Jones	460732	NP - New patient	106 - Sasaki, Elizabeth M
9/2/2021	Kim Jones	447316	NP - New patient	1099 - Narayan, Anand
9/17/2021	Kim Jones	448959	NP - New patient	1100 - Narayan, Anand
10/20/2021	Kim Jones	453735	NP - New patient	1101 - Narayan, Anand
9/2/2021	Kim Jones	447316	NP - New patient	1102 - Narayan, Anand
9/17/2021	Kim Jones	448959	NP - New patient	1103 - Narayan, Anand
10/20/2021	Kim Jones	453735	NP - New patient	1104 - Narayan, Anand
3/29/2021	Kim Jones	427875	NP - New patient	1105 - Narayan, Anand
7/22/2021	CRINE - Mike	442258	NP - New patient	1106 - Narayan, Anand
8/9/2021	CRINE - Mike	444456	NP - New patient	1107 - Narayan, Anand
8/13/2021	CRINE - Mike	444547	NP - New patient	100 - Sasaki, Elizabeth M

excel

NikolinoDE
Gold Contributor
Jan 13, 2023
SUZETTE1971
Here is a simplified suggested solution with the formula =SUBRESULT.
You must first insert the data into a spreadsheet.
Sample file is included.

Hope I was able to help you with this info.

NikolinoDE
I know I don't know anything (Socrates)

Simplified_Number_Patients.xlsx11 KB
- SUZETTE1971
  Copper Contributor
  Jan 13, 2023
  NikolinoDE
  
  I don't want to add up all referring providers at once but with 12,000 rows of information in colum "E" Referring Providers, I want it to add up like this. Can you help me please?
  - NikolinoDE
    Gold Contributor
    Jan 13, 2023
    SUZETTE1971
    As I already informed you in my suggested solution, you have to put your data in a table, then you can simply filter by name, as in the example file, and the number of visits by the selected person will appear immediately.
    Create and format tables
- Raja544357
  Copper Contributor
  Jan 13, 2023
  It doesn't seem to be what he needs
Raja544357
Copper Contributor
Jan 13, 2023
hello, I'm not sure if I misunderstood, Use function--sumif Should be able to complete

Forum Discussion

Consolidating Data

Share

Resources