SUMIFS in VBA - return values on multiple columns dependent on a cell header

[ramona_manea]

Hello all. I have a raw data of sales (This year, last year, budget ) by stores (380 stores), by each day of the 2022 year.
I am trying to build a macro that brings me the data from that RAW database to another sheet , where the data is presented a little differently, analyzed etc .
First I tried to do it with simple SUMIFS formula in Excel and the file almost crashed. Then I created a VBA with SUMIFS , but also it takes a lot of time to update. And I could get past that problem, but I do not know how to make it populate data in all columns based on a header .

I need to present the data with same main categories, but by days . In order for this to happen I am just copy - pasting in VBA the script I wrote and changing the columns in which I want the VBA to put the data and also the cell of date that is changing. I am writing below my script. Could you please help? I am sure there is an easier way .

Thanks !

Sub Sumifs()

Dim CA_istoric As Range
Dim CA_real As Range
Dim CA_buget As Range
Dim Clienti_istoric As Range
Dim Clienti_real As Range
Dim Cantitate_istoric As Range
Dim Cantitate_real As Range
Dim Magazine As Range
Dim Data As Range
Dim LastRow As Long
Dim Counter As Integer

Set CA_istoric = Sheets("RAW").Range("T:T")
Set CA_real = Sheets("RAW").Range("J:J")
Set CA_buget = Sheets("RAW").Range("Q:Q")
Set Clienti_istoric = Sheets("RAW").Range("W:W")
Set Clienti_real = Sheets("RAW").Range("M:M")
Set Cantitate_istoric = Sheets("RAW").Range("X:X")
Set Cantitate_real = Sheets("RAW").Range("N:N")
Set Magazine = Sheets("RAW").Range("F:F")
Set Data = Sheets("RAW").Range("B:B")
LastRow = ActiveSheet.Range("I6").End(xlDown).Row

For Counter = 6 To LastRow

ActiveSheet.Range("M" & Counter) = WorksheetFunction.Sumifs(CA_real, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("M4") + 1)
ActiveSheet.Range("S" & Counter) = WorksheetFunction.Sumifs(Clienti_real, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("M4") + 1)
ActiveSheet.Range("U" & Counter) = WorksheetFunction.Sumifs(Cantitate_real, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("M4") + 1)
ActiveSheet.Range("N" & Counter) = WorksheetFunction.Sumifs(CA_buget, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("M4") + 1)
ActiveSheet.Range("Q" & Counter) = WorksheetFunction.Sumifs(CA_istoric, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("M4") + 1)
ActiveSheet.Range("AC" & Counter) = WorksheetFunction.Sumifs(Clienti_istoric, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("M4") + 1)
ActiveSheet.Range("V" & Counter) = WorksheetFunction.Sumifs(Cantitate_istoric, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("M4") + 1)

ActiveSheet.Range("AG" & Counter) = WorksheetFunction.Sumifs(CA_real, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("AG4") + 1)
ActiveSheet.Range("AH" & Counter) = WorksheetFunction.Sumifs(CA_buget, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("AG4") + 1)
ActiveSheet.Range("AK" & Counter) = WorksheetFunction.Sumifs(CA_istoric, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("AG4") + 1)
ActiveSheet.Range("AM" & Counter) = WorksheetFunction.Sumifs(Clienti_real, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("AG4") + 1)
ActiveSheet.Range("AO" & Counter) = WorksheetFunction.Sumifs(Cantitate_real, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("AG4") + 1)
ActiveSheet.Range("AP" & Counter) = WorksheetFunction.Sumifs(Cantitate_istoric, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("AG4") + 1)
ActiveSheet.Range("AW" & Counter) = WorksheetFunction.Sumifs(Clienti_istoric, Magazine, ActiveSheet.Range("H" & Counter).Value, Data, ActiveSheet.Range("AG4") + 1)

Next Counter


End Sub

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
4						01/10/2022																				02/10/2022
5	Store code	Store name				Actual	Buget			N-1		Clienti		#Nr articole	#Nr articole N-1							Clienti N-1				Actual	Buget			N-1		Clienti		#Nr articole	#Nr articole N-1							Clienti N-1
6	1	A
7	2	B
8	3	C
9	4	D
10	5	E
Oct

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AG4	Cell Value	="duminică"	text	NO
AG4	Cell Value	="duminica"	text	NO
M4	Cell Value	="duminică"	text	NO
M4	Cell Value	="duminica"	text	NO

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Data N-1	Data	Zi Saptamana	Cod Magazin	Magazin	Cod SAP	Denumire SAP	Format	Regiune	CA TTC Total			Nr Clienti Total	Cantitate Total			Buget TTC Total			CA TTC N-1 Total			Nr Clienti N-1 Total	Cantitate N-1 Total
2		01/10/2022	Saturday			1	A			102,872.80			2,082	14,649			86,344.72			108,051.49			2,015	16,589
3		01/10/2022	Saturday			2	B			0.00			0	0			62,176.87			0.00			0	0
4		02/10/2022	Sunday			1	A			17,310.97			0	0			31,775.07			14,024.86			0	0
5		02/10/2022	Sunday			2	B			24,588.44			65	156			0.00			21,331.12			42	132
6		02/10/2022	Sunday			3	C			18,141.71			42	93			0.00			11,907.75			44	119
7		03/10/2022	Sunday			1	A			12,102.08			58	158			0.00			8,832.65			30	83
8		03/10/2022	Sunday			2	B			11,570.20			58	143			0.00			8,580.39			31	88
RAW

 

[steve the fish]

Im pretty sure it will be easier to use a pivot table on your raw data.

 

[ramona_manea]

I have extra columns and other dependent calculations in the sheet I want to bring data to…. The raw data is extracted from a system AS IS