Help with VBA code to replace COUNTIFS formula that is crashing Excel

[Joyner]

Hello everyone and thank you for the help.

I am trying to copy this formula down a column to count certain occurrences and I need to copy it down for up to 400k rows:

=COUNTIFS($O$4:O4,O4,$W$4:W4,W4,$AB$4:AB4,AB4)

Note the ranges are expanding as the formula is copied down.

For whatever reason this particular formula crashes Excel after giving me the "Out of Memory" error - I assuming Excel's resources don't appreciate 400K countifs formulas.

I am just a beginner at VBA, can anyone point me in the right direction about how I would write code to count the occurrences I am trying to do with the COUNTIFS formula and put the number in a column, if it matters, I would like the numbers to go in column AC, starting in row 4.

Thank you

 

[sheetspread]

Try the pivot table instead. Powerpivot is even better for this.

 

[Joyner]

Try the pivot table instead. Powerpivot is even better for this.

Thanks, but I don't think a pivot table will give me the type of count I am looking for with the expanding ranges and multiple criteria I need and I need to use the count for other things also.

 

[sheetspread]

It can handle expanding ranges/multi criteria, post a smaller sample and I'll show you. Otherwise powerpivot, power query, or SQL will definitely work.

 

[Joyner]

Thanks, I would like to see how that is done. I am pretty good with Pivot Tables but I can't envision how a Pivot Table could do what I need. Power Pivot and Power Query I am just now learning and could see maybe inside the data model it is possible but again I would have no idea how to do that.

For posting the sample, I'll see if I can - maybe there are options for me to get a sample posted, but my work has our computers locked down tight and we can't add any programs.

Thanks again for the help.

 

[Joyner]

[TABLE="class: grid"]
<tbody>[TR]
[TD="class: xl65"]Id
[/TD]
[TD="class: xl65"]Name
[/TD]
[TD="class: xl65"]Date
[/TD]
[TD="class: xl65"]Amount
[/TD]
[TD="class: xl65, width: 96, bgcolor: #5B9BD5"]Occurrence
[/TD]
[/TR]
[TR]
[TD="class: xl66"]00001
[/TD]
[TD="class: xl67"]Smith, John
[/TD]
[TD="class: xl68, align: right"]10/31/2014
[/TD]
[TD="class: xl67, align: right"]-1286.4
[/TD]
[TD="class: xl67, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]00002
[/TD]
[TD="class: xl70, bgcolor: transparent"]Doe, Jane
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]9/5/2014
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]-129.11
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66"]00001
[/TD]
[TD="class: xl67"]Smith, John
[/TD]
[TD="class: xl68, align: right"]10/31/2014
[/TD]
[TD="class: xl67, align: right"]1286.4
[/TD]
[TD="class: xl67, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]00002
[/TD]
[TD="class: xl70, bgcolor: transparent"]Doe, Jane
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]9/5/2014
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]129.11
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66"]00001
[/TD]
[TD="class: xl67"]Smith, John
[/TD]
[TD="class: xl68, align: right"]10/31/2014
[/TD]
[TD="class: xl67, align: right"]-1286.4
[/TD]
[TD="class: xl67, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]00002
[/TD]
[TD="class: xl70, bgcolor: transparent"]Doe, Jane
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]9/5/2014
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]-129.11
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66"]00002
[/TD]
[TD="class: xl67"]Doe, Jane
[/TD]
[TD="class: xl68, align: right"]9/5/2014
[/TD]
[TD="class: xl67, align: right"]129.11
[/TD]
[TD="class: xl67, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]00001
[/TD]
[TD="class: xl70, bgcolor: transparent"]Smith, John
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]10/31/2014
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1286.4
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66"]00003
[/TD]
[TD="class: xl67"]Deal, Big
[/TD]
[TD="class: xl68, align: right"]12/5/2014
[/TD]
[TD="class: xl67, align: right"]-5961.54
[/TD]
[TD="class: xl67, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]00002
[/TD]
[TD="class: xl70, bgcolor: transparent"]Doe, Jane
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]9/5/2014
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]-129.11
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl66"]00001
[/TD]
[TD="class: xl67"]Smith, John
[/TD]
[TD="class: xl68, align: right"]10/31/2014
[/TD]
[TD="class: xl67, align: right"]-1286.4
[/TD]
[TD="class: xl67, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]00004
[/TD]
[TD="class: xl70, bgcolor: transparent"]Too, Me
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]10/10/2014
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]-170.79
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66"]00001
[/TD]
[TD="class: xl67"]Smith, John
[/TD]
[TD="class: xl68, align: right"]10/31/2014
[/TD]
[TD="class: xl67, align: right"]-1286.4
[/TD]
[TD="class: xl67, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]00001
[/TD]
[TD="class: xl70, bgcolor: transparent"]Smith, John
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]12/5/2014
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]-121
[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66"]00001
[/TD]
[TD="class: xl67"]Smith, John
[/TD]
[TD="class: xl68, align: right"]10/24/2014
[/TD]
[TD="class: xl67, align: right"]-490.06
[/TD]
[TD="class: xl67, align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]


Wow, look at me, I did it!

So Sheetspread, hopefully you have enough here to see what I need. I need the occurrence column to count the occurrence of each positive and each negative, I am matching offsets. Let me know if you need anything else.

 

[sheetspread]

You'll just have to add an order field to the source data, then insert a PT, drag all the fields in row labels, and also amount into values, change the value setting to count, and running total off order:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Order	Id	Name	Date	Amount	Occurrence
2	1	1	Smith, John	10/31/2014	-1286.4	1		Order	Id	Name	Date	Amount	Count of Amount
3	2	2	Doe, Jane	9/5/2014	-129.11	1		1	1	Smith, John	10/31/2014	-1286.4	1
4	3	1	Smith, John	10/31/2014	1286.4	1		2	2	Doe, Jane	9/5/2014	-129.11	1
5	4	2	Doe, Jane	9/5/2014	129.11	1		3	1	Smith, John	10/31/2014	1286.4	1
6	5	1	Smith, John	10/31/2014	-1286.4	2		4	2	Doe, Jane	9/5/2014	129.11	1
7	6	2	Doe, Jane	9/5/2014	-129.11	2		5	1	Smith, John	10/31/2014	-1286.4	2
8	7	2	Doe, Jane	9/5/2014	129.11	2		6	2	Doe, Jane	9/5/2014	-129.11	2
9	8	1	Smith, John	10/31/2014	1286.4	2		7	2	Doe, Jane	9/5/2014	129.11	2
10	9	3	Deal, Big	12/5/2014	-5961.5	1		8	1	Smith, John	10/31/2014	1286.4	2
11	10	2	Doe, Jane	9/5/2014	-129.11	3		9	3	Deal, Big	12/5/2014	-5961.54	1
12	11	1	Smith, John	10/31/2014	-1286.4	3		10	2	Doe, Jane	9/5/2014	-129.11	3
13	12	4	Too, Me	10/10/2014	-170.79	1		11	1	Smith, John	10/31/2014	-1286.4	3
14	13	1	Smith, John	10/31/2014	-1286.4	4		12	4	Too, Me	10/10/2014	-170.79	1
15	14	1	Smith, John	12/5/2014	-121	1		13	1	Smith, John	10/31/2014	-1286.4	4
16	15	1	Smith, John	10/24/2014	-490.06	1		14	1	Smith, John	12/5/2014	-121	1
17								15	1	Smith, John	10/24/2014	-490.06	1
Sheet6

 

[Joyner]

Wow, brilliant sheetspread, works perfect - I would have never thought about a running total of a sequence number (order field) and think I am still trying to wrap my head around the logic...

Thanks again, I learned something valuable today...