Forum Discussion

CatherineMadden's avatar
CatherineMadden
Brass Contributor
Dec 29, 2023
Solved

Sum Formulas

Hello,    I have a simple spreadsheet that I want to use the SUM function. However, the cells I want to SUM have an IF formula in them already. When I use the SUM function, it returns zero. So how ...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Dec 29, 2023

    CatherineMadden Your IF formulas return texts rather than numbers. That's why SUM returns zero. Remove all the quotation marks surrounding the ones and zeroes and the SUM function will work.

     

    Having said that, I would recommend you to do away with those nested IF formulas all-together. The attached file contains an alternative method to achieve your goal. 

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 29, 2023

CatherineMadden 

Please ignore this post.  It is not aimed at you but, rather, uses your problem formulation to explore some approaches to getting an 'array of arrays' result without Excel throwing a wobbly (complaining about nested arrays).

 

The following solutions use:-

1. REDUCE/HSTACK to workaround BYCOL with array output

2. MAKEARRAY and INDEX

3. MAP, with thunks to hold an array of row ranges

 

To my mind, each of these 'solutions' is far more complicated and difficult to build than it should be; a simple formula returning a simple array of arrays in all that is needed!

 

REDUCE/BYROW 

= DROP(
    REDUCE("", weekStart, LAMBDA(acc,w,
      HSTACK(acc, BYROW(data, LAMBDA(d,
        COUNTIFS(d, ">=" & w, d, "<" & w+5)
      )))
   )),
  ,1)

MAKEARRAY

= MAKEARRAY(ROWS(data), 4, LAMBDA(employee,weekNumber,
    LET(
      empData, INDEX(data, employee, 0),
      start,   INDEX(weekStart, weekNumber),
      COUNTIFS(empData, ">=" & start, empData, "<" & start+5)
    )
   ))

MAP/thunk

= LET(
    Thunkλ, LAMBDA(x,LAMBDA(x)),
    week,   IF(ROW(data), weekStart),
    empϑ,   BYROW(data, Thunkλ),
    dϑ,     IF(weekStart, empϑ),
    MAP(week, dϑ,
      LAMBDA(w,ϑ, COUNTIFS(ϑ(),">=" & w, ϑ(), "<" & w+5))
    )
  )

 

Any thoughts?

Resources