Forum Discussion

jrsilverio's avatar
jrsilverio
Copper Contributor
Mar 14, 2024

HOW TO LOCK FORMULA SOMASE AND UPDATE IT BY ITSELF?

I made a SOMASE formula to pull data from 2 spreadsheets, the problem is that I always need to replace the files with current ones with new information, and the SOMASE formula in the base spreadsheet...
BI & Data Analysis
excel
Macros and VBA
jrsilverio's avatar
jrsilverio
Copper Contributor
Mar 20, 2024

NikolinoDE 

The error occurs, the formula appears (#name):

 

=SOMASE(INDIRECT("[" & "almox.xls" & "]Sheet1!$C:$C"); A2; INDIRECT("[" & "almox.xls" & "]Sheet1!$H:$H"))

 

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Mar 20, 2024

jrsilverio 

It seems like the #NAME? error occurs because the SOMASE function might not be recognized in your version of Excel. The correct function name for summing with conditions in Excel is typically SUMIFS, not SOMASE. Let's correct the formula accordingly:

=SUMIFS(INDIRECT("[" & "almox.xls" & "]Sheet1!$H:$H"), INDIRECT("[" & "almox.xls" & "]Sheet1!$C:$C"), A2)

 

=SUMIFS(INDIRECT("[" & "snd.xls" & "]Sheet1!$H:$H"), INDIRECT("[" & "snd.xls" & "]Sheet1!$C:$C"), A2)

Make sure to replace "almox.xls" and "snd.xls" with the actual file names of your Excel workbooks. Also, ensure that the referenced workbooks are open while using the INDIRECT function in your formulas. If the workbooks are closed, the formula may return a #REF! error.