Changing a Sumifs to a Sumproduct

[Joshua Kao]

Hi, could someone please help me convert the sumifs formula in VBA, to a sumproducts please? Thank you.

ActiveCell.FormulaR1C1 = _
"=SUMIFS('Closing Stock.xlsx'!C3,'Closing Stock.xlsx'!C1,RC[-4])"

 

[kweaver]

What is the objective of the sumproduct? The sumifs doesn't include a range.

 

[Joshua Kao]

The range is C3. Its not cell C3, its Column C3. The formula is supposed to be in a macro.

 

[Fluff]

Why do you want it changed to sumproduct?
Also do you really have a sheet called "Closing Stock.xlsx"?

 

[kweaver]

The range is C3. Its not cell C3, its Column C3. The formula is supposed to be in a macro.

Using "C3" in the formula just looks at the cell not the range "C:C"

 

[Fluff]

@kweaver
The formula is in R1C1 notation so C3 is column 3

 

[kweaver]

Ah, right...I overlooked the R1C1 notation.

 

[Joshua Kao]

@Fluff i need it to be in sumproduct as when i run the macro, i generally do not have the "closing stock" spreadsheet open, so a #value error would come up. So I want it to be in a sumproduct format to prevent that error.

 

[Fluff]

What is the name of the sheet?

 

[Joshua Kao]

the name of the sheet that the macro is running in is "consolidated sheet", but the sumifs that im currently using is taking a sum from the other workbook called "closing stocks". I have attached both mini sheets below.

Consolidated Workbook.xlsm
	A	B	C	D	E	F	G
1	Medicine Code	Medicine Name	Category	Unit	Stock System	Stock Count	% Diff
2	1ACO	ACOLIC SYR (SIME / DICY)	ml	GENERAL	#VALUE!	3670	#VALUE!
3	1AER	AERIUS SYR (DESLORATADINE)	ml	GENERAL	#VALUE!	180	#VALUE!
4	1ALZ	ZENTEL 400MG/10ML (ALBENDAZOLE)	bot	GENERAL	#VALUE!	2	#VALUE!
ComparisonM2K

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4	Cell Value	between -0.0000000000000000001 and -9.99999999999999	text	NO
G4	Cell Value	<=-10	text	NO
G4	Cell Value	=0	text	NO
G4	Cell Value	between 0.00000000000001 and 9.99999999999999	text	NO
G4	Cell Value	>=10	text	NO
G3	Cell Value	between -0.0000000000000000001 and -9.99999999999999	text	NO
G3	Cell Value	<=-10	text	NO
G3	Cell Value	=0	text	NO
G3	Cell Value	between 0.00000000000001 and 9.99999999999999	text	NO
G3	Cell Value	>=10	text	NO
G2	Cell Value	between -0.0000000000000000001 and -9.99999999999999	text	NO
G2	Cell Value	<=-10	text	NO
G2	Cell Value	=0	text	NO
G2	Cell Value	between 0.00000000000001 and 9.99999999999999	text	NO
G2	Cell Value	>=10	text	NO

Closing Stock.xlsx
	A	B	C	D
2	Code	Name	Stock	Unit
3	3DRE-S	DRESSING - SIMPLE	-1,342	tx
4	3NEBU-P	NEB - NEBULISATION PROCEDURE	-996	neb
5	3ECG	ECG PROCEDURE	-469	tx
closing stock-PS