Forum Discussion

Yousef70's avatar
Yousef70
Brass Contributor
Jul 19, 2023
Solved

If formula

Dear Sirs …. Kindly need your help with below table to distinguish between + or - numbers in the result cell to take + value cell keep in mind the following If formula used in result cell ( if there...
microsoft 365
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Jul 23, 2023

    Alternative

    = LET(
    s, BYROW(data, LAMBDA(d, MAX(SUM(d), 0))),
    VSTACK(s, SUM(s))
  )
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jul 22, 2023

Yousef70 Then you should consider using SUMIF like in the example in the picture below. This will sum only positive amounts.

 

 

Yousef70's avatar
Yousef70
Brass Contributor
Jul 22, 2023

Riny_van_Eekelen 

 

Dear Riny …

 

Hooray, it did work perfectly , thanks a million 

 

one more question :

can the result be conditioned to font color ??!! if possible for example :

1- payment honored , green font , accounted for

2- payment not honored , red font , not accounted for 

3- payment not yet due , black font , not accounted for  

  • Yousef70's avatar
    Yousef70
    Brass Contributor
    Jul 22, 2023
    Dear Hans ...

    Many thanks for your help
    I found that the status sheet easier to use
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 22, 2023

    Yousef70 

    The attached workbook contains two sheets.

     

    The first sheet has manually colored amounts. It uses a custom VBA function SumGreenPositive to sum the positive values in green.

    Disadvantage: if you change the font color of an amount, Excel will not automatically update the result of the formula. You'll have to wait until Excel recalculates, or press F9.

    And you'll have to allow macros for it to work. Macros don't work at all in Excel Online and in Excel for Android and iPhone/iPad.

     

    The second sheet has an extra column with the status.

    The amounts in column A have not been colored manually, but by using two conditional formatting rules.

    The positive "Honored" values are summed by an ordinary SUMIFS formula.

    Advantage: if you change the status of an amount, the result of the formula will be updated automatically.

  • Yousef70's avatar
    Yousef70
    Brass Contributor
    Jul 22, 2023

    Dear Hans …

    thanks for your input

    what I meant in color conditioning is to enter a number in green , red & black color to effect the result cell input for example :-
    1- If I enter a number in green , to accounted for in result cell ( calculated in total )
    2- If I enter a number in red , not to be accounted for in result cell ( not calculated in total )
    3- If I enter a number in black , not to be accounted for in result cell ( not calculated in total )

    If you have a sample , please , share it with me

    Thanks in advance 


     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 22, 2023

    Yousef70 

    I'd add a column with the status as text.

    You can then use SUMIF or SUMIFS to add amounts with a specific status, and use conditional formatting to color the amounts.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 22, 2023

    Yousef70 Use that expression as you please. It's not mine 🙂

     

    VBA = Visual Basic for Applications. In short, a programming language used in Excel to automate all sorts of processes that otherwise can't be achieved by regular formulas.

  • Yousef70's avatar
    Yousef70
    Brass Contributor
    Jul 22, 2023
    Dear Riny …

    I understand you my friend , I like your expression " not my cup of tea " may I use it

    What is VBA though ?
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 22, 2023

    Yousef70 Excel isn't very good at calculating based on colors. That would require VBA which is not my cup of tea.

Resources