Question description:
This user has given permission to use the problem statement for this
blog.
Hello,
I have a big spreadsheet in which I need to know how many patients came in each month based on provider. I am using the following formula but I still get the N/A error.
=INDEX('No Show Appts Data'!D:E, MATCH(1, 'No Show Appts Data'!D:D='No Shows Data'!M2)*('No Shows Data'!E:E='No Shows Data'!N1),0))
No Show Appts Data is the name of the sheet where Column D is Month Year of date in question and Column E is the provider. No Shows Data is the sheet where I am making all the formula calculation where Column M is Month and Year and Column N is the provider in question and therefore M2 is the month in question and N1 the provider in question.
How do I fix this error?
Thanks
Solved by F. H. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/03/2018 - 03:47
Hi
User
09/03/2018 - 03:47
hello
Excelchat Expert
09/03/2018 - 03:47
How may i help you
Excelchat Expert
09/03/2018 - 03:48
can you show some sample data
User
09/03/2018 - 03:48
i dont know why my formula is wrong here is a simpler version
User
09/03/2018 - 03:48
=INDEX(D:E, MATCH(1,E:E=$H$2)*(D:D=G3), 0)
User
09/03/2018 - 03:49
thats the data
Excelchat Expert
09/03/2018 - 03:50
put the formula that you are using
User
09/03/2018 - 03:50
i need to know the count for each month by provider
User
09/03/2018 - 03:52
I ALREADY DID
Excelchat Expert
09/03/2018 - 03:53
it can be done without using index match
User
09/03/2018 - 03:53
i know it can be done using countifs but I have huge amounts of data
User
09/03/2018 - 03:54
what other suggestion do you have
Excelchat Expert
09/03/2018 - 03:54
ohk
Excelchat Expert
09/03/2018 - 03:54
i think countifs would have been better solution
Excelchat Expert
09/03/2018 - 03:54
what do you say?
Excelchat Expert
09/03/2018 - 03:55
you there?
User
09/03/2018 - 03:56
hello?
User
09/03/2018 - 03:56
sorory
User
09/03/2018 - 03:56
the chat froze
Excelchat Expert
09/03/2018 - 03:56
ohk
Excelchat Expert
09/03/2018 - 03:56
i think countifs would have been better solution
Excelchat Expert
09/03/2018 - 03:56
what do you say?
User
09/03/2018 - 03:57
i need an easier way cuz countifs will need to be specified for each provider and month
User
09/03/2018 - 03:57
and I have HUGE data sets...this only an example to get the formula right
User
09/03/2018 - 03:57
so if i use countifs each cell will need to be different and specfied
Excelchat Expert
09/03/2018 - 03:58
ohk
User
09/03/2018 - 03:58
hello?
Excelchat Expert
09/03/2018 - 03:58
yes
Excelchat Expert
09/03/2018 - 03:59
i dont think each cell would be different
User
09/03/2018 - 03:59
can you help me come up with a different solution that isnt countifs
Excelchat Expert
09/03/2018 - 03:59
formula can be dragged down i think
User
09/03/2018 - 03:59
yes it is because each month and each provider is diff
User
09/03/2018 - 03:59
=COUNTIFS(!D:D, "82017", 'E:E "Mcnamara")
User
09/03/2018 - 04:00
it can!! but that doesnt help
User
09/03/2018 - 04:00
arent you supposed to be an expert?
User
09/03/2018 - 04:00
of course formuas cna be dragged down but the month changes
Excelchat Expert
09/03/2018 - 04:00
yes that i what i am telling you mate
Excelchat Expert
09/03/2018 - 04:00
let me build formula for you
User
09/03/2018 - 04:01
ok fine
User
09/03/2018 - 04:01
please build it
User
09/03/2018 - 04:02
please let me know when you are done
Excelchat Expert
09/03/2018 - 04:03
sure
User
09/03/2018 - 04:04
we only have 3 min left please help
Excelchat Expert
09/03/2018 - 04:04
time will extend
User
09/03/2018 - 04:04
never mind I extended it
Excelchat Expert
09/03/2018 - 04:04
dont worry
User
09/03/2018 - 04:05
can you help me out or not? im currently at work and cannot be too long on here
Excelchat Expert
09/03/2018 - 04:05
i am working on my desktop excel
Excelchat Expert
09/03/2018 - 04:05
i am helping you out mate
User
09/03/2018 - 04:05
ok thanks a ton
User
09/03/2018 - 04:05
let me know
Excelchat Expert
09/03/2018 - 04:08
did you have a look?
Excelchat Expert
09/03/2018 - 04:08
i used countifs to do this
Excelchat Expert
09/03/2018 - 04:08
let me know if this helps you out
User
09/03/2018 - 04:08
give me a second
User
09/03/2018 - 04:11
it doesnt
User
09/03/2018 - 04:11
cuz i like i have HUGE data sets
Excelchat Expert
09/03/2018 - 04:11
it will work on huge data sets as well
Excelchat Expert
09/03/2018 - 04:11
is it not giving you correct data?
Excelchat Expert
09/03/2018 - 04:12
i am trying my best to help you out
Excelchat Expert
09/03/2018 - 04:12
and this is the best method i think we should use
Excelchat Expert
09/03/2018 - 04:16
does it help you buddy?
Excelchat Expert
09/03/2018 - 04:16
are you there?
User
09/03/2018 - 04:16
yes thanks a ton
Excelchat Expert
09/03/2018 - 04:18
thanks
Excelchat Expert
09/03/2018 - 04:18
please do give good rating
Excelchat Expert
09/03/2018 - 04:18
it really helps
Excelchat Expert
09/03/2018 - 04:20
are you there?
Excelchat Expert
09/03/2018 - 04:24
please do give good rating
Excelchat Expert
09/03/2018 - 04:24
it really helps a lot
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
