Thanks:  0
Likes:  0

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

1. 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. 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. 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. 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. 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!