Forum Discussion

Cas Ros's avatar
Cas Ros
Copper Contributor
Feb 28, 2018
Solved

Need help writing an excel formula

I'm trying to make a formula that sums the data below onto a single field.

if the value in column A is bigger than the value in Column B of the same row i want the value of column A - the value of column B.

and if B is the same or higher as A in the same row i want the value of column A - 1.

i need to do this for all rows in a selected range and sum it in a single field.

A B
3 5
3 0
4 0
5 2
3 0
3 1

* this is just a small simplified version

 

edit:

i got something like this atm but since IF only returns teh 1st element i dont get the right result.

=IF(Tabel6[total needed] > Tabel6[parents got];Tabel6[total needed] - Tabel6[parents got];Tabel6[total needed] - 1)

Formulas & Functions
Need Help
  • Willy Lau's avatar
    Willy Lau
    Feb 28, 2018
    1. Select a cell of your table(I guess table6)
    2. Table Tools in Ribbon > Design tab > Table Style Options > check Total Row
    3. Go to the total row of the table, replace the subtotal formula with 
      =SUMPRODUCT(N([total needed] > [parents got]) * ([total needed] - [parents got]) + N(NOT([total needed] > [parents got])) * ([total needed] - 1))

     

    Edition: Using the formula in total row of the table will make confusion.  You may use the above formula in any other cells.

     

    Also, use SUM function to wrap the formula in your post with Ctrl + Shift + Enter

    {=SUM(IF(Tabel6[total needed] > Tabel6[parents got],Tabel6[total needed] - Tabel6[parents got],Tabel6[total needed] - 1))}

     note: I use comma (,) to seperate parameters; you may need to change it back to semi-colon(;) if that works on your side.

4 Replies

Sort By
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Feb 28, 2018

    Cas,

     

    wrap {SUM()} around it.

    {=SUM(IF(Tabel6[total needed] > Tabel6[parents got];Tabel6[total needed] - Tabel6[parents got];Tabel6[total needed] - 1))}
  • Willy Lau's avatar
    Willy Lau
    Steel Contributor
    Feb 28, 2018
    1. Select a cell of your table(I guess table6)
    2. Table Tools in Ribbon > Design tab > Table Style Options > check Total Row
    3. Go to the total row of the table, replace the subtotal formula with 
      =SUMPRODUCT(N([total needed] > [parents got]) * ([total needed] - [parents got]) + N(NOT([total needed] > [parents got])) * ([total needed] - 1))

     

    Edition: Using the formula in total row of the table will make confusion.  You may use the above formula in any other cells.

     

    Also, use SUM function to wrap the formula in your post with Ctrl + Shift + Enter

    {=SUM(IF(Tabel6[total needed] > Tabel6[parents got],Tabel6[total needed] - Tabel6[parents got],Tabel6[total needed] - 1))}

     note: I use comma (,) to seperate parameters; you may need to change it back to semi-colon(;) if that works on your side.

Resources