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:

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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:

Watch MrExcel Video

Forum statistics

Threads
1,101,891
Messages
5,483,528
Members
407,397
Latest member
HerbA

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top