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.
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]John Reference cell for name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PH Reference cell for Interview Code[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]RI Reference cell for Interview Code[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
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
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.
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]John Reference cell for name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PH Reference cell for Interview Code[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]RI Reference cell for Interview Code[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
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
