CODEWITHSUNDEEP
excel
Steve K
Steve K

Reputation: 387

Is it possible to maintain values across cells in excel?

Here's what I'm working with:

    A     B     C     D      E     F
1 |  2/22    |    2/28   |    3/1     |
2 |Est | Act | Est | Act |  Est | Act | 
3 | 8    10     2     1      1     0  |

In this example, the date cells are merged, the goal being that the date should span across its Estimated and Actual columns beneath.

What I'm trying to do is to get the sum of these values where:

If today is less than or equal to the dates in column A here, sum the Actual values since that date is past. Otherwise, if today is greater than the dates in column A, sum the Estimated values, since I'm trying to get a total of hours including the forecasted ones.

So I wrote a formula which I thought would do this:

=SUM(IF(TODAY()<=A1:F1,IF(A2:F2="Act",A3:F3),IF(TODAY()>A1:F1,IF(A2:F2="Est",A3:F3))))

The day I started this was 2/28, and my hope would be the total would be 11 (Act) + 1 Est = 12.

In this case, though, since my formula looks at all of the cells underneath the "merged" ones, I'm not sure its possible. If I print out the value of A1 and B1 in this example, I'd get Feb-14 and Jan-00 respectively which is a problem for my formula

Does this make sense? Is it possible to get around it?

Upvotes: 1

Views: 85

Answers (1)

Jerry
Jerry

Reputation: 71558

Maybe something a bit like that:

=SUMPRODUCT((A1:F1<=TODAY())*(A1:F1<>"")*B3:G3)+SUMPRODUCT((A1:F1>TODAY())*A3:F3)

The multiplications are what apply the conditions.

A1:F1<=TODAY() gets all the dates that are today and before.

A1:F1<>"" ensures that the blank cells are not included (in merged cells, only the top rightmost cell address has the value and the others are considered blank).

B3:G3 is a range that starts with actual values.

SUMPRODUCT((A1:F1<=TODAY())*(A1:F1<>"")*B3:G3) gets the sum of all the actual values.

A little less complicated SUMPRODUCT((A1:F1>TODAY())*A3:F3) gets the sum of all estimated values.

In more detail, A1:F1<=TODAY() returns an array of true and false, as does A1:F1<>"" and A1:F1>TODAY(). In your example, we have A1:F1<=TODAY() which is:

{2/22,0,2/28,0,3/1,0}<=TODAY()

which gives the array {TRUE,TRUE,TRUE,TRUE,FALSE,TRUE}. Similarly, `A1:F1<>"" gives the array:

{TRUE,FALSE,TRUE,FALSE,TRUE,FALSE}

When multiplied together, they get converted into 1s and 0s, 1 being true and 0 being false. (A1:F1<=TODAY())*(A1:F1<>"") thus gives the array:

{1,0,1,0,0,0}

because you get 1 only when TRUE gets multiplied by another TRUE and you get 0 as soon as there is a FALSE involved in the multiplication.

The array then becomes:

{1,0,1,0,0,0}*B3:G3

Which is:

{1,0,1,0,0,0}*{10,2,1,1,0,0}

And giving:

(10,0,1,0,0,0}

SUMPRODUCT then adds those numbers to give 11`

Likewise, if we have SUMPRODUCT((A1:F1>TODAY())*A3:F3), we get:

SUMPRODUCT({FALSE,FALSE,FALSE,FALSE,TRUE,FALSE}*{8,10,2,1,1,0})

Which gives 1.

Upvotes: 1

Related Questions