Changing a Sumifs to a Sumproduct

Joshua Kao

New Member
Joined
Feb 14, 2021
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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])"
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
What is the objective of the sumproduct? The sumifs doesn't include a range.
 
Upvote 0
The range is C3. Its not cell C3, its Column C3. The formula is supposed to be in a macro.
 
Upvote 0
Why do you want it changed to sumproduct?
Also do you really have a sheet called "Closing Stock.xlsx"?
 
Upvote 0
@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.
 
Upvote 0
What is the name of the sheet?
 
Upvote 0
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
ABCDEFG
1Medicine CodeMedicine NameCategoryUnitStock SystemStock Count% Diff
21ACOACOLIC SYR (SIME / DICY)mlGENERAL#VALUE!3670#VALUE!
31AERAERIUS SYR (DESLORATADINE)mlGENERAL#VALUE!180#VALUE!
41ALZZENTEL 400MG/10ML (ALBENDAZOLE)botGENERAL#VALUE!2#VALUE!
ComparisonM2K
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G4Cell Valuebetween -0.0000000000000000001 and -9.99999999999999textNO
G4Cell Value<=-10textNO
G4Cell Value=0textNO
G4Cell Valuebetween 0.00000000000001 and 9.99999999999999textNO
G4Cell Value>=10textNO
G3Cell Valuebetween -0.0000000000000000001 and -9.99999999999999textNO
G3Cell Value<=-10textNO
G3Cell Value=0textNO
G3Cell Valuebetween 0.00000000000001 and 9.99999999999999textNO
G3Cell Value>=10textNO
G2Cell Valuebetween -0.0000000000000000001 and -9.99999999999999textNO
G2Cell Value<=-10textNO
G2Cell Value=0textNO
G2Cell Valuebetween 0.00000000000001 and 9.99999999999999textNO
G2Cell Value>=10textNO


Closing Stock.xlsx
ABCD
2CodeNameStockUnit
33DRE-SDRESSING - SIMPLE-1,342tx
43NEBU-PNEB - NEBULISATION PROCEDURE-996neb
53ECGECG PROCEDURE-469tx
closing stock-PS
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top