Index Match to count occurances

[kellyp1603]

Good Morning,

I am looking for a formula which will match name to name and count how many occurrences of 'x' within a certain range. I am thinking it will be an index match with a count if but i'm struggling to put it together.

I have a list of names and I want to know how many holidays they have used by quarter without having a count if on the same page and looking up to that.

[TABLE="width: 717"]
<colgroup><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]02/01/2017[/TD]
[TD="align: right"]03/01/2017[/TD]
[TD="align: right"]04/01/2017[/TD]
[TD="align: right"]05/01/2017[/TD]
[TD="align: right"]06/01/2017[/TD]
[/TR]
[TR]
[TD]Agent[/TD]
[TD]Holidays Used[/TD]
[TD][/TD]
[TD]Agent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kelly[/TD]
[TD]?[/TD]
[TD][/TD]
[TD]Kelly[/TD]
[TD]H[/TD]
[TD]PM[/TD]
[TD]AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help would be much appreciated!

Thanks
Kelly

 

[04nimmot]

Hi Kelly, my first instinct would be to use COUNTIF, however I have supplied both the countif and sumproduct method for you. Either of these would be appropriate.
Index and match is more of lookup tool, granted it could be used with multiple criteria, however even then I would probably choose sumproduct or countifs.

=COUNTIF(A1:E1,"=x")
=SUMPRODUCT(--(A1:E1="x"))

If you need any further help, or I have misunderstood the request let me know.

 

[kellyp1603]

Thank you for coming back to me. My issue is that I have a large amount of data containing the information on 1 sheet and a second sheet which is a summary of the first sheet. Previously I used a count if on the data sheet and a VLOOKUP on the overview sheet to pull back the information. I was hoping to rid the countif on the first sheet and just keep all of my formulas on the overview sheet for tidiness. Sheet 1 contains a list of names then rows containing dates eg row 1 and holidays used in row 2. I want the overview sheet to tell me how many holidays a name has used for each quarter - hope this makes sense!

 

[04nimmot]

I see what you have, hopefully the below should suit

=SUMPRODUCT((Sheet1!$A$2:$A$3=$A2)*(Sheet1!$B$2:$F$3="x"))
A2 contains Kelly, but could be replaced with "Kelly" instead of the cell reference if you wish.

on sheet1 I have as below, on sheet2 just a list of names with this formula:

Sheet1
[TABLE="width: 483"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD="align: right"]01.01.2017
[/TD]
[TD="align: right"]02.01.2017
[/TD]
[TD="align: right"]03.01.2017
[/TD]
[TD="align: right"]04.01.2017
[/TD]
[TD="align: right"]05.01.2017
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Kelly
[/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Tom
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]