# Formula help!

#### MarkAndrews

##### Well-known Member
I have this table
FE Analysis.xls
EFGHIJ
1AprilMayJuneJulyAugustSeptember
272%106%100%96%79%95%
3121%80%68%
4170%153%108%120%75%
582%133%82%35%
660%107%60%36%
7108%76%116%96%76%45%
8128%164%
9139%124%94%114%80%111%
1073%132%76%100%83%61%
11139%208%124%72%95%104%
1290%110%52%
13120%100%55%
14173%118%150%208%
1588%75%
1683%150%108%72%92%74%
17125%104%68%
1894%100%63%
19108%200%
2086%106%128%167%84%52%
21100%76%100%84%88%33%
22211%113%100%72%63%46%
23143%120%72%113%150%146%
2464%100%
25180%170%132%100%
26 100%171%100%
2780%
2870%
29112%128%112%111%97%71%
Input Table

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### MarkAndrews

##### Well-known Member
I realise my Hlookup is wrong

Is there an alternative?

TIA

#### Richard Schollar

##### MrExcel MVP
Hi Mark

Maybe try this:

Code:
``=COUNTIF(INDEX(\$B\$2:\$M\$28,,MATCH(\$P3,\$B\$1:\$M\$1,0)),">0")``

EDITED: Domenic's formula much better, hence have modified mine

#### Domenic

##### MrExcel MVP
Try...

Q3, copied down:

=COUNT(INDEX(\$E\$2:\$J\$29,0,MATCH(P3,\$E\$1:\$J\$1,0)))

Hope this helps!

#### MarkAndrews

##### Well-known Member

Thanks Guys

I have amended to suit, however - Its not counting all months

?

#### MarkAndrews

##### Well-known Member
FE Analysis.xls
PQRS
1CountOfFECountOfFE
3Jan0
4Feb0
5Mar0
6Apr0
7May18
8Jun0
9Jul0
10Aug0
11Sep0
12Oct0
13Nov0
14Dec0
Input Table

#### Richard Schollar

##### MrExcel MVP

Mark

Apologies, should have noticed earlier - across the top in B1:M1 you have full month names, but in P3:P14 you only have short month names, so amend one or other.

By the way, I don't think you want to include row 29 in the Index.

#### MarkAndrews

##### Well-known Member
Mark

Apologies, should have noticed earlier - across the top in B1:M1 you have full month names, but in P3:P14 you only have short month names, so amend one or other.

By the way, I don't think you want to include row 29 in the Index.

Fantastic - thankyou guys

Thanks Richard (Congrats on your MVP too!)

Here's my table now
FE Analysis.xls
PQRS
1CountOfFECountOfFE
3January0
4February0
5March0
6April17
7May18
8June15
9July21
10August21
11September23
12October0
13November0
14December0
Input Table

#### MarkAndrews

##### Well-known Member
I'm struggling with the Manchester one now, as this works from this table
FE Analysis.xls
ABCDEFGHIJKLMN
31ManchesterJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberAverage
32DCruise87%100%80%73%100%92%89%
33EYoung91%100%116%116%108%69%100%
34GDavies115%112%52%93%
35JCruise61%81%68%73%100%56%73%
36JCunliffe78%53%150%74%94%90%
37MGregory74%94%68%50%72%
38NAkhtar72%72%44%104%104%79%79%
39RGoacher20%180%75%92%
40SAmeen87%64%71%74%
41SDhinsa70%60%68%89%88%75%
42Average   79%82%70%85%108%76%   83%
Input Table

Replies
5
Views
399
Replies
4
Views
426
Replies
13
Views
803
Replies
5
Views
327
Replies
2
Views
211