Issues with count ifs

alex456

New Member
Joined
Jun 30, 2019
Messages
2
Hi

first post, so apologies if I make a mistake

I need to count the following

MeterP7 2016 -2017P7 2016 -2017P7 2016 -2017P8 2016 -2017P8 2016 -2017P9 2016 -2017P9 2016 -2017
9761600165167165217233207212
9763705237220272240267194
9766902254245363376371323
9767107298336412412409399
9773304371355393355399554481
9784803416374351365374400344
9789802185186179354345258

<tbody>
</tbody>






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
 
Last edited by a moderator:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,306
Welcome to Mr Excel forum

See if this does what you need

A
B
C
D
E
F
G
H
1
Meter​
P7 2016 -2017​
P7 2016 -2017​
P7 2016 -2017​
P8 2016 -2017​
P8 2016 -2017​
P9 2016 -2017​
P9 2016 -2017​
2
9761600​
165​
167​
165​
217​
233​
207​
212​
3
9763705​
237​
220​
272​
240​
267​
194​
4
9766902​
254​
245​
363​
376​
371​
323​
5
9767107​
298​
336​
412​
412​
409​
399​
6
9773304​
371​
355​
393​
355​
399​
554​
481​
7
9784803​
416​
374​
351​
365​
374​
400​
344​
8
9789802​
185​
186​
179​
354​
345​
258​
9
10
11
12
13
P7 2016 -2017​
P8 2016 -2017​
P9 2016 -2017​
14
9766902​
3​
1​
2​
15
9767107​
3​
2​
1​
16
9789802​
2​
2​
2​
17

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

New Member
Joined
Jun 30, 2019
Messages
2
Thank you so much, this worked perfectly - you are a star
 
Last edited by a moderator:

Forum statistics

Threads
1,078,239
Messages
5,339,027
Members
399,274
Latest member
WilliamWavehill

Some videos you may like

This Week's Hot Topics

Top