Hello,

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

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

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

G2 =COUNTIF($F$2:$F2,"Both")

However, the question now is:

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

As I see it, the formula for generating the running total would be:

Where A is the column with the date data, and G is the running total column.

See attached

2013-06-05a-EE-function-for-crit.xlsx

=SUM((YEAR(A$2:A2)=2013)*(

confirmed with CTRL+SHIFT+ENTER

.....but I prefer bhess1/fanpages' approach, for a running total why re-calculate all the previous rows each time, that's just wasted effort, you can simply ascertain whether

=SUM((YEAR(A2)=2013)*(WEEK

regards, barry

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Expanding on

bhess1's suggestion, combining the test for the year (2013) & the day of the week (not Sunday) within the same initial IF condition:Please paste this formula into cell [G2]:

=IF(AND(YEAR(A2)=2013,TEXT

Then copy cell [G2] to the Windows Clipboard & paste down column [G] to cell [G11] (i.e. [G3:H11]).

Also copy [G2] to [G15:G24] (or as far down column [G] as required).

(Please see the attached workbook)

BFN,

fp.

Q-28149033.xlsx