Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: COUNTIF question? Trying to total all values within a block of cells

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default COUNTIF question? Trying to total all values within a block of cells

    Hello,
    I have a strange possible COUNTIF question. I am trying to count all cells within a sequence once a sequence starts (I know I am not explaining it well...). For example, in a column there are the following totals:
    A
    A
    A = 3
    B
    B = 2
    C
    C
    C
    C = 4

    I would love to figure out a way to total each block as it occurs. In the above instance, this would also occur:

    C
    C = 2
    D = 1
    C
    C = 2

    So a D occurred and broke up the sequence of C totals.

    I am going to try to attach a worksheet that sort of shows what I am trying to do. Note that I am trying to tally up the Numbers in column C. I created column D with totals at the end of each sequence.

    Any help is appreciated! Thank you so much, have a great holiday.

  2. #2
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF question? Trying to total all values within a block of cells

    Sorry, just read the rules and I cannot attach a file! Posting a screen shot. As you can see I am trying to tally the block of numbers in column C. I included a simple tally at the end of the string in column D. I hope this helps...

    https://ibb.co/cYqrq6

  3. #3
    Board Regular
    Join Date
    Aug 2012
    Posts
    245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF question? Trying to total all values within a block of cells

    You could use:

    Code:
    =IF(AND(C2=C3,D2=D3),"",COUNTIFS(C:C,C2,D:D,D2))
    This assumes first formula row is row 2. If not, increase all '2's to the first row number, and all '3's to that number plus 1.

    Cheers
    JB

  4. #4
    New Member
    Join Date
    Nov 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF question? Trying to total all values within a block of cells

    This is amazing! Thank you for this. I never would have come up with this equation in a million years.

    I modified it slightly and I came up with this:

    =IF(AND(C2=C3),"",COUNTIFS($C$2:C2,C2))

    So the totals start from $C$2 and then it continually adds. Here is the problem I am running into...for the first blocks it works fine. I have linked a screen shot that I can explain.

    https://ibb.co/b7J8L6


    For instance, starting with C143 to CC148 it adds this string up (1222542 occurs 6 times). It then moves to 1222537 which occurs 2 times. Then 1222536 which occurs 12 times. All dandy so far.

    We encounter another string of 12222542 and this occurs 14 times. However since the range is looking for everything from C2 to the current cell, it is adding in the prior instance of 1222542 6 occurrences for a total of 20 (row 176).

    I'm stumped. Honestly what you have done so far has been so helpful, I appreciate this so much! A million thank yous.

  5. #5
    Board Regular
    Join Date
    Aug 2012
    Posts
    245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF question? Trying to total all values within a block of cells

    Ahh, didn't realise a number could reappear later.

    Although saying that, I think if column B is different, even when C is the same, my original formula should work.

    If sometimes B and C reappear together, that that's a thinker!

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
  •