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

tylerfutures

New Member
Joined
Nov 22, 2017
Messages
4
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
cYqrq6
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...
cYqrq6


https://ibb.co/cYqrq6
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top