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])"
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,445
Office Version
  1. 365
  2. 2010
What is the objective of the sumproduct? The sumifs doesn't include a range.
 

Joshua Kao

New Member
Joined
Feb 14, 2021
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
The range is C3. Its not cell C3, its Column C3. The formula is supposed to be in a macro.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Why do you want it changed to sumproduct?
Also do you really have a sheet called "Closing Stock.xlsx"?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,445
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
@kweaver
The formula is in R1C1 notation so C3 is column 3
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,445
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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

Joshua Kao

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
What is the name of the sheet?
 

Joshua Kao

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

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

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
Top