**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.*