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]

Id	Name	Date	Amount	Occurrence
00001	Smith, John	10/31/2014	-1286.4	1
00002	Doe, Jane	9/5/2014	-129.11	1
00001	Smith, John	10/31/2014	1286.4	1
00002	Doe, Jane	9/5/2014	129.11	1
00001	Smith, John	10/31/2014	-1286.4	2
00002	Doe, Jane	9/5/2014	-129.11	2
00002	Doe, Jane	9/5/2014	129.11	2
00001	Smith, John	10/31/2014	1286.4	2
00003	Deal, Big	12/5/2014	-5961.54	1
00002	Doe, Jane	9/5/2014	-129.11	3
00001	Smith, John	10/31/2014	-1286.4	3
00004	Too, Me	10/10/2014	-170.79	1
00001	Smith, John	10/31/2014	-1286.4	4
00001	Smith, John	12/5/2014	-121	1
00001	Smith, John	10/24/2014	-490.06	1

<tbody>
</tbody>

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