Issues with count ifs

[alex456]

Hi

first post, so apologies if I make a mistake

I need to count the following

[TABLE="width: 665"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Meter[/TD]
[TD="class: xl65, width: 137"]P7 2016 -2017[/TD]
[TD="class: xl65, width: 100"]P7 2016 -2017[/TD]
[TD="class: xl65, width: 118"]P7 2016 -2017[/TD]
[TD="class: xl65, width: 116"]P8 2016 -2017[/TD]
[TD="class: xl65, width: 127"]P8 2016 -2017[/TD]
[TD="class: xl65, width: 111"]P9 2016 -2017[/TD]
[TD="class: xl65, width: 114"]P9 2016 -2017[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9761600[/TD]
[TD="class: xl66, align: right"]165[/TD]
[TD="class: xl66, align: right"]167[/TD]
[TD="class: xl66, align: right"]165[/TD]
[TD="class: xl66, align: right"]217[/TD]
[TD="class: xl66, align: right"]233[/TD]
[TD="class: xl66, align: right"]207[/TD]
[TD="class: xl66, align: right"]212[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9763705[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]237[/TD]
[TD="class: xl66, align: right"]220[/TD]
[TD="class: xl66, align: right"]272[/TD]
[TD="class: xl66, align: right"]240[/TD]
[TD="class: xl66, align: right"]267[/TD]
[TD="class: xl66, align: right"]194[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9766902[/TD]
[TD="class: xl66, align: right"]254[/TD]
[TD="class: xl66, align: right"]245[/TD]
[TD="class: xl66, align: right"]363[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]376[/TD]
[TD="class: xl66, align: right"]371[/TD]
[TD="class: xl66, align: right"]323[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9767107[/TD]
[TD="class: xl66, align: right"]298[/TD]
[TD="class: xl66, align: right"]336[/TD]
[TD="class: xl66, align: right"]412[/TD]
[TD="class: xl66, align: right"]412[/TD]
[TD="class: xl66, align: right"]409[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]399[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9773304[/TD]
[TD="class: xl66, align: right"]371[/TD]
[TD="class: xl66, align: right"]355[/TD]
[TD="class: xl66, align: right"]393[/TD]
[TD="class: xl66, align: right"]355[/TD]
[TD="class: xl66, align: right"]399[/TD]
[TD="class: xl66, align: right"]554[/TD]
[TD="class: xl66, align: right"]481[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9784803[/TD]
[TD="class: xl66, align: right"]416[/TD]
[TD="class: xl66, align: right"]374[/TD]
[TD="class: xl66, align: right"]351[/TD]
[TD="class: xl66, align: right"]365[/TD]
[TD="class: xl66, align: right"]374[/TD]
[TD="class: xl66, align: right"]400[/TD]
[TD="class: xl66, align: right"]344[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9789802[/TD]
[TD="class: xl66, align: right"]185[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]186[/TD]
[TD="class: xl66, align: right"]179[/TD]
[TD="class: xl66, align: right"]354[/TD]
[TD="class: xl66, align: right"]345[/TD]
[TD="class: xl66, align: right"]258[/TD]
[/TR]
</tbody>[/TABLE]






if column title =

P7 2016 -2017

and

Row =

9789802


Then how many values have I got

So the answer would be 2 for P7, 2 for P8 etc.


The full spreadsheet is 244 rows x 1100 columns

I have tried sumproduct, countifs, etc but cannot get them to work

Thanks very much

 

[Marcelo Branco]

Welcome to Mr Excel forum

See if this does what you need

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Meter​

[/td][td]

P7 2016 -2017​

[/td][td]

P7 2016 -2017​

[/td][td]

P7 2016 -2017​

[/td][td]

P8 2016 -2017​

[/td][td]

P8 2016 -2017​

[/td][td]

P9 2016 -2017​

[/td][td]

P9 2016 -2017​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

9761600​

[/td][td]

165​

[/td][td]

167​

[/td][td]

165​

[/td][td]

217​

[/td][td]

233​

[/td][td]

207​

[/td][td]

212​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

9763705​

[/td][td][/td][td]

237​

[/td][td]

220​

[/td][td]

272​

[/td][td]

240​

[/td][td]

267​

[/td][td]

194​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

9766902​

[/td][td]

254​

[/td][td]

245​

[/td][td]

363​

[/td][td][/td][td]

376​

[/td][td]

371​

[/td][td]

323​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

9767107​

[/td][td]

298​

[/td][td]

336​

[/td][td]

412​

[/td][td]

412​

[/td][td]

409​

[/td][td][/td][td]

399​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

9773304​

[/td][td]

371​

[/td][td]

355​

[/td][td]

393​

[/td][td]

355​

[/td][td]

399​

[/td][td]

554​

[/td][td]

481​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

9784803​

[/td][td]

416​

[/td][td]

374​

[/td][td]

351​

[/td][td]

365​

[/td][td]

374​

[/td][td]

400​

[/td][td]

344​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

9789802​

[/td][td]

185​

[/td][td][/td][td]

186​

[/td][td]

179​

[/td][td]

354​

[/td][td]

345​

[/td][td]

258​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td][/td][td]

P7 2016 -2017​

[/td][td]

P8 2016 -2017​

[/td][td]

P9 2016 -2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

9766902​

[/td][td="bgcolor:#D9D9D9"]

3​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

9767107​

[/td][td="bgcolor:#D9D9D9"]

3​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

9789802​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in B14 copied across and down (gray area)
=COUNTIFS(INDEX($B$2:$H$8,MATCH($A14,$A$2:$A$8,0),0),">0",$B$1:$H$1,B$13)

M.

 

[alex456]

Thank you so much, this worked perfectly - you are a star