Count a range of codes whose ID > 10: COUNTIF with INDEX & MATCH as Criteria?

[mateorafa]

Hi guys,

Im getting crazy trying the whole weekend to solve this issue. Hope somebody may help!

1) I have a TABLE in which I have 3 main headers:

• [ID]: Numeric field from 1 to n
• [CONCEPT]: Text field with the description of each ID

• : Text field with initials that represent each CONCEPT
  [/LIST]
  
  2) In other sheet, I have a [U][B]RANGE[/B][/U] (in a row) with lots of cells , each of those cells containing a different CODE
  
  3) I need a formula (without creating any auxiliary columns or rows) that gives me the number of CODES in that RANGE with ID>10. Something like this: [B]COUNTIF (RANGE of CODES,"CODES whose ID>10")[/B]
  
  4) I have tried combinations of COUNTIF+INDEX+MATCH with no result. Any ideas?
  
  Many thanks in advance!

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this example help you?

Excel 2010
	A	B	C	D	E	F
1	ID	CONCEPT	CODE		Code1	5
2	1	Concept1	Code1		Code2
3	6	Concept2	Code2		Code3
4	11	Concept3	Code3
5	16	Concept4	Code1
6	1	Concept5	Code3
7	23	Concept6	Code2
8	11	Concept7	Code2
9	16	Concept8	Code3
Sheet1

Cell Formulas
Range	Formula
F1	=SUMPRODUCT(COUNTIFS(Table1[ID],">10",Table1 ,E1:E3))[/XD][/XR][/RANGE]

 

[mateorafa]

It worked perfect Andrew, thanks a lot!!
I wonder the solution was around sumproduct, but I have still to understand the logic behind the use of this function for these cases
Kind regards!
PD: sorry for the delay in my response, but I was unable to connect to the Internet until now