Forum Discussion
Nesting "=VALUE" functions into "=SUMIF" formulas
Hi Everyone, Wondering if the community can help me out on an issue I'm coming up with. I'm trying to get some quantities data from one of our CAD (computer assisted design) models - I work at a...
Sumif requires range references for sum range and criteria range (versus a calculated array that the value function would return), so you would need a different approach.
You could use power query to pull the data file in and change the column formats. Or, you could perform a conditional sum using array multiplication:
if you have a newer version of excel (dynamic array)
=Sum((Value(A1:A10)>100) * B1:B10)
otherwise:
=Sumproduct((Value(A1:A10)>100) * B1:B10)
You could use power query to pull the data file in and change the column formats. Or, you could perform a conditional sum using array multiplication:
if you have a newer version of excel (dynamic array)
=Sum((Value(A1:A10)>100) * B1:B10)
otherwise:
=Sumproduct((Value(A1:A10)>100) * B1:B10)
JMB17 Thanks for the response! I ended up just changing the format in Revit (by multiplying or dividing by 1) in order for excel to read as numbers - actually pretty good solution as quantities can update automatically from there. Got the idea from how excel converts cells from text to numbers when you get that little warning symbol that pops up next to your imported data.