Problem with INDEX, MATCH, and a function to count data from a table

**Pentasyllabic** · 05-22-2012, 02:56 PM

I am new to the forums, and this is my first post, so I apologize if I don't come off as clear as I am supposed to. I have been working through a project involving sales and amounts, but have become stuck. I need to find a way to be able to type in a person's name (It needs to work for any name within the table), and be able to have specific information appear in the cells below said name. I'll include the Excel file and my tasks as an attachment to aid clarification. I know how to use MATCH and INDEX in order to pull a name from the table, but the way the table is designed seems to mean I have to use a function that can count in the same formula. I think I am supposed to use DCOUNT, although I also think that may only work with numbers. From what I've been able to come up with so far, my first step would be to count how many times a salesperson's name appears in the column in order to display how many sales they completed, which would be the first bit of information that would be presented when a specific name from the table was typed in. I would also need to show the average billed and the sum of sales. Again, I have included attachments to better explain what I am asking. One is the word document with my instructions (I am on number 8), and the other is the Excel file. Thank you very much for your time.

**NBVC** · 05-22-2012, 03:42 PM

We cannot do your homework for you! Sorry.

This should be doable with the D-Functions, DAVERAGE, DCOUNT and DSUM. Review those function.

You can find tutorials and samples on how to use them on the web...

Here for instance: http://www.techonthenet.com/excel/formulas/dsum.php

**Pentasyllabic** · 05-22-2012, 03:50 PM

Thanks for your reply. The book I've been using goes through all of those functions, but just not in the way it seems I need to use them. Can I use the INDEX and MATCH functions in the same formula as the D-Functions?

**NBVC** · 05-22-2012, 03:55 PM

Not really, unless you are looking at one unique number to return in the list... INDEX?MATCH returns one specific value from an intersection of criteria matches.

an alternative to D functions can be SUMIF or SUMIFS, COUNTIF or COUNTIFS, AVERAGEIF or AVERAGEIFS.. or SUMPRODUCT when more complex criteria are in play...

**Pentasyllabic** · 05-22-2012, 04:10 PM

I am able to get those functions to work, just not having them change when I enter a different name. I'm kind of at a loss, but I guess I'll just keep trying different things. I really appreciate your help.

**NBVC** · 05-22-2012, 04:16 PM

If, for example you have the name: Collins in H2, then the formula to count number of times that name appears is:

=COUNTIF(Table2[Salesperson],H2)

**Pentasyllabic** · 05-22-2012, 04:39 PM

That is the part that I understand. I am able to get the answer for an individual person by entering a formula like the one given above, but if I were to type in, say, "Simon" as the name in cell A102, the number that was correct for Collins in cell A103 wouldn't change. The results are supposed to change depending on which name is entered in cell A102. That is where I've been stuck all morning. I've reread the book numerous times, but the only time they mention tying a name to a result is done with INDEX and MATCH, which is why I was asking about those earlier, but that table was laid out differently and such a method wouldn't help in this situation. This is an online class so I can't really go and ask my instructor about it for help.

**NBVC** · 05-22-2012, 04:48 PM

Make sure you have Automatic Calculations On... Formulas Tab, Calculation Options section.

**Pentasyllabic** · 05-22-2012, 04:59 PM

Automatic Calculations are on. I actually checked that earlier thinking that might have been the problem.

**NBVC** · 05-22-2012, 05:07 PM

Then I am confused as to what the issue is...

Check my attached.

Change the name in H2. Does H3 not change value?

also, your formula must be outside the table....

**Pentasyllabic** · 05-22-2012, 05:16 PM

I am confused as to what the issue was as well. I applied the formula to the cells I am using, and it works now. I think I may have been using "=COUNTIF(Table2[Salesperson],B10) thinking that B10 was the cell containing the criteria I wanted to count. I can't thank you enough for your help, NBVC. I truly appreciate it. I think figuring that solved any other questions that I had. Thanks again and have an awesome day.

Problem with INDEX, MATCH, and a function to count data from a table

LinkBack

Thread Tools

Rate This Thread

Display

Problem with INDEX, MATCH, and a function to count data from a table

Re: Problem with INDEX, MATCH, and a function to count data from a table

Re: Problem with INDEX, MATCH, and a function to count data from a table

Re: Problem with INDEX, MATCH, and a function to count data from a table

Re: Problem with INDEX, MATCH, and a function to count data from a table

Re: Problem with INDEX, MATCH, and a function to count data from a table

Re: Problem with INDEX, MATCH, and a function to count data from a table

Re: Problem with INDEX, MATCH, and a function to count data from a table

Re: Problem with INDEX, MATCH, and a function to count data from a table

Re: Problem with INDEX, MATCH, and a function to count data from a table

Re: Problem with INDEX, MATCH, and a function to count data from a table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions