Forum Discussion

karwooddave's avatar
karwooddave
Copper Contributor
Sep 21, 2022

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 an architectural firm - and when I import some data (area and volumetric measurements) from that model into excel it comes in as text. I'm trying to nest the value function within both a sumif, then roundup, functions but seemingly get caught at the sumif.

 

Not sure if thats clear or not but happy to provide more details if need be.

 

Thanks,

Dave

 

excel
Formulas and Functions

2 Replies

Sort By
  • JMB17's avatar
    JMB17
    Bronze Contributor
    Sep 21, 2022
    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)
    • karwooddave's avatar
      karwooddave
      Copper Contributor
      Sep 22, 2022

      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.

Resources