VBA code for SUMIFS function

[Akira181]

I'm very new to VBA and I'm currently struggling with how to do a SUMIFS code with a table of data. I can follow along some tutorials and examples to get it to work for a single cell, but struggling when it comes to a full table of data.

I'm trying to find the total cost for each unique reference in Sheet 2 and paste it into the corresponding Week cell for that Ref in Sheet 1. Below is what my data is going to look like:

Sheet 2
The columns with "..." in them have data but aren't relevant for what I'm trying to do. There are potentially an unlimited number of rows and I would like to find the total Cost for each unique reference in Column B

Book1
	A	B	C	D	E	F	G	H
1
2
3		Ref	…	…	Cost	Date	CW	…
4		001			£ 1.00	02/01/2021	1
5		001			£ 2.00	07/01/2021	2
6		003			£ 3.00	12/01/2021	3
7		005			£ 4.00	12/01/2022	3
8		003			£ 5.00	13/01/2022	3
9		002			£ 6.00	05/02/2022	6
10		003			£ 7.00	06/02/2022	6
11		004			£ 8.00	06/02/2022	6
BOM

Sheet 3
This is what I want the end result to look like. I can do it with a formula but it needs to be in VBA. Can someone help a novice?

Book1
	A	B	C	D	E	F	G	H	I	J
1
2					Month	Jan	Jan	Jan	Jan	Feb
3		Ref	…	…	WK.	1	2	3	4	5
4		001				100	100	0	0	0
5		002				0	0	0	0	100
6		003				0	0	200	0	100
7		004				0	0	100	0	0
8		005				0	0	0	0	100
Sheet3

Cell Formulas
Range	Formula
F2:J2	F2	=TEXT(DATE(YEAR(NOW()),1,F3*7-2),"mmm")
F4:J8	F4	=SUMIFS(BOM!E4:E11,BOM!B4:B11,Sheet3!B4:B8,BOM!G4:G11,Sheet3!F3:J3)
Dynamic array formulas.

 

[Akira181]

Glad to be of help. I did realise after I had written the code that it could be slightly more efficient when writing out the results by looping through the refarr first, however if the code works then the lack of perfection is probably irrelevant.

Not a problem, any lack of perfection will definitely go unnoticed. My modifications and Frankenstein smashing of multiple codes together is definitely more inefficient than anything you could do. I'm still getting to grips with the basics :P

Sorry to grave dig this but the turn of the year has raised a bug. Items in the BOM sheet from last year are getting displayed in table in Sheet 3 as future items. As in, Dec 21 items are getting displayed in the table in sheet3 when it should only have this years data in it.

Unfortunately, I cannot change the BOM data as the bulk of it is auto-generated. How difficult would it be to modify the sumifs VBA to ignore any dates that don't match the current year?

The current year is on a different sheet just now but I can put it in F2 for ease

 

[offthelip]

you can easily exclude data that is from the previous year by adding an if statement around the bit that loads thie dictionalry like this:

For i = 4 To lastrow
 If Year(inarr(i, 6)) = Year(Date) Then   'add this line
 
   If dict.exists(inarr(i, 2)) Then
     Erase temparr
     ReDim temparr(1 To cwmax)  ' to pass values to dictionary
     temparr = dict.Item(inarr(i, 2)) ' load dictionary item into temp array
     temparr((inarr(i, 7))) = temparr(inarr(i, 7)) + inarr(i, 5)  ' add cost to total using week no as index
     dict.Item(inarr(i, 2)) = temparr  ' write tempory array back to dictionary
   Else
     Erase temparr
     ReDim temparr(1 To cwmax)  ' to pass values to dictionary
     temparr(inarr(i, 7)) = inarr(i, 5)   ' add first cost into total using week no as index
     dict.Add inarr(i, 2), temparr()
    End If
 End If                                    'addthis line
 Next i

 

[Akira181]

Was determined to figure out myself why it wasn't working yesterday and gave up till this morning. Fresh set of eyes and I realised it was working, I just didn't delete the existing wrong data in the Sheet3 table, 2 hours I won't be getting back ?‍

Bought a VBA for Dummies book, if I can get halfway to your skill level by the end of the year, I'll consider that a success!

Thanks again, a great help as always!