Data Collection for personal.

Newbienew

Active Member
Joined
Mar 17, 2017
Messages
376
Office Version
  1. 2016
Platform
  1. Windows
Why hello to all. I am in need of some assistance. I am trying to accomplish 3 things in one formula. Basically nesting
I want to count (a person name as well as the possible typo error of an extra space after the name, with dates only, sorted by the interview code that was given.

So if John has three appointments for the month but he is work with someone from last month which is a carryover (C/O) I need to count only the ones that have dates and not the ones say C/O in the cell. Then I need to count the ones with his name and dates sorted by the interview code. So out of the three with dates two of them have a interview code of PH and the last one would be RI.

John Reference cell for name
PH Reference cell for Interview Code2
RI Reference cell for Interview Code1

<tbody>
</tbody>

Note: There are other interview codes that may change

1. I want to countif the persons name and the possibility of at type error of extra space. Which I believe is this formula
=IF(E57="",0,SUM(COUNTIFS($B$3:$B$53,E57&"*"
2. added I am trying to only count dates in one column and minus the C/O (carryover) and No Show entries. Which I believe is this formula
-COUNTIFS($C$3:$C$53,"C/O",$G$3:$G$53,A60)-COUNTIFS($C$3:$C$53,"",$G$3:$G$53,A60)
3. With trying to tally the number of interview codes by the names with dates only. Which this is the last formula but only the first part is different.
=COUNTIF(G$1:G$54,A60)-COUNTIFS($C$3:$C$53,"C/O",$G$3:$G$53,A60)-COUNTIFS($C$3:$C$53,"",$G$3:$G$53,A60)

If there is a way for me to share what I am trying to do please let me know as I really need help Thank you so much in advance

Meant to add I tried to Nest it my self or attempted in the only way I know and this is what i got
=IF(A69="",0,SUM(COUNTIFS($B$3:$B$53,A69&"*",COUNTIF(G$1:G$54,A60)-COUNTIFS($C$3:$C$53,"C/O",$G$3:$G$53,A60)-COUNTIFS($C$3:$C$53,"",$G$3:$G$53,A60)))

https://www.dropbox.com/s/04r69ydm98ubz2g/DATA COLLECTION.xlsx?dl=0
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top