Link to home
Start Free TrialLog in
Avatar of Steve_Brady
Steve_BradyFlag for United States of America

asked on

Need help combining 4 Excel formulas please

Note: this is a follow-up to this thread:
    Use a function as the criteria in the Excel =COUNTIF() function

Hello,

The screenshot (with details) shown below is from an Excel (2010) spreadsheet which is attached. The purpose of the spreadsheet is to:

    Display the Running Total of Col A dates which are in 2013 but are not Sundays.

The objective has already been achieved (I believe) in the attachment with the results shown here in Col G:

User generated imageThe data range includes Rows 2:100 and has the following columns:

    Col A_a random date between 2011-Jan-01 & 2013-Jun-05
    Col B_the day of the week
    Col C_spacer
    Col D_shows dates in 2013 ("`" if not)
    Col E_shows non-Sundays ("`" if not)
    Col F_shows when both Cols D & E are True ("`" if not)
    Col G_shows running total of "Boths" in Col F

    A2    =RANDBETWEEN(40544,41430)
    B2    =TEXT(A2,"ddd")
    C2       n/a
    D2    =IF(YEAR(A2)=2013,2013,"`")
    E2    =IF(B2<>"Sun","NotSun","`")
    F2    =IF(AND(D2=2013,E2="NotSun"),"Both","`")
    G2    =COUNTIF($F$2:$F2,"Both")


However, the question now is:

    Can the same endpoint be achieved in a single column?

I have always assumed that any number of formulas—each of which builds upon the previous—can be nested into a single formula. However, that may be a very naïve assumption and not true. If it is true though, I cannot figure out how to put the formulas together in this case.

Thanks
2013-06-05a-EE-function-for-crit.xlsx
SOLUTION
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Steve_Brady

ASKER

Great responses. Thanks.