Issues with count ifs
Results 1 to 3 of 3

Thread: Issues with count ifs
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Issues with count ifs

    Hi

    first post, so apologies if I make a mistake

    I need to count the following

    Meter P7 2016 -2017 P7 2016 -2017 P7 2016 -2017 P8 2016 -2017 P8 2016 -2017 P9 2016 -2017 P9 2016 -2017
    9761600 165 167 165 217 233 207 212
    9763705 237 220 272 240 267 194
    9766902 254 245 363 376 371 323
    9767107 298 336 412 412 409 399
    9773304 371 355 393 355 399 554 481
    9784803 416 374 351 365 374 400 344
    9789802 185 186 179 354 345 258






    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 RoryA; Jul 1st, 2019 at 06:30 AM.

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Issues with count ifs

    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.

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issues with count ifs

    Thank you so much, this worked perfectly - you are a star
    Last edited by RoryA; Jul 1st, 2019 at 06:29 AM. Reason: Remove unnecessary quote

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •