Projecting revenue in month based on date. Also total if/then stuff.

[MrDetermination]

Hi!

I have manually put numbers in P5 through R12 for the sake of illustration. I want P5 to have the value of K5 when F5 is the same month as D3. I want Q6 to have the same value as K6 when F6 is the month after D3. I need the others to be "zero" for the sake of summing the columns.

Also, I want totals in U and V to be if/then totals. The totals are the same as K however U5 would not populate here because there are numbers in I5 and or J5. V7 would not populate because there are no values input in I7 or J7.


Pipeline.xlsx

[daffodil11]

Like this?

Pipeline(1).xlsx

I ran into some issues, where P was blank, K was blank, and so U and V were blank.

[MrDetermination]

Sheesh... Bad communication AND a bad cell reference on my part. Sorry I want P5 to have the value of K5 when F5 is the same month as D3. I want Q6 to have the same value as K6 when F6 is the month after D3.

Thanks for trying!

[daffodil11]

I'll rewrite it in 10 minutes when I go on Lunch.

[daffodil11]

P5:

=IF(DATE(YEAR($F5),MONTH($F5),DAY(1))=DATE(YEAR($D$3),MONTH($D$3)+COLUMN(A1)-1,DAY(1)),$K5,"")

You can copy that into Q6, R7, P8, Q9, R10, etc..

[daffodil11]

And here's the finished product.

Pipeline(1).xlsx

[MrDetermination]

Thanks! Almost there, I think.

That totally clears up the second part of the ask!

On the month 1-3 though... only one column should populate in each row. P should populate if F(any) and D(3) are in the same month. Q should populate if F = current month + 1(. Month 3 should populate if F = Current month + 2(months).

If I populate all the cells P-R with: =IF(DATE(YEAR($F5),MONTH($F5),DAY(1))=DATE(YEAR($D$3),MONTH($D$3)+COLUMN(A1)-1,MONTH(1)),$K5,"")

All the cells populate with the same value.

[daffodil11]

No no no, the formula is designed to be copied and pasted (or dragged) across the other cells.

The reference symbols "$" are strategically placed within the formula so that adapts as it's moved.

P5:
=IF(DATE(YEAR($F5),MONTH($F5),DAY(1))=DATE(YEAR($D$3),MONTH($D$3)+COLUMN(A1)-1,DAY(1)),$K5,"")

P6:
=IF(DATE(YEAR($F6),MONTH($F6),DAY(1))=DATE(YEAR($D$3),MONTH($D$3)+COLUMN(A2)-1,DAY(1)),$K6,"")

Q5:
=IF(DATE(YEAR($F5),MONTH($F5),DAY(1))=DATE(YEAR($D$3),MONTH($D$3)+COLUMN(B1)-1,DAY(1)),$K5,"")

Q6:
=IF(DATE(YEAR($F6),MONTH($F6),DAY(1))=DATE(YEAR($D$3),MONTH($D$3)+COLUMN(B2)-1,DAY(1)),$K6,"")

Notice how the formula has changed?

It should be as simple as copying the formula into P5, and then copying and pasted that cell into the others.

[daffodil11]

The only change you should need to make is for S5: =sumif(P5:R5,">0"), and then copy that formula down to S12.

[daffodil11]

Here's the same sheet with the changes to Column S, and the formula I provided earlier copied into P5, and then dragged down and over.

Pipeline(1).xlsx

The part of the formula with column(a1)-1 is just a dynamic design that really means 0 in the first column, 1 in the second, and 2 in the third. It's nice way to prevent having to manually change the formulas for Q and R.

When the same formula is copied anywhere into Q, the reference changes to column(b1)-1. Column(b1) = 2. 2 - 1 = 1. Now the formula is targeting the month + 1.

When the same formula is copied anywhere into R, the reference changes again to column(C1)-1. Column(C1) = 3. 3 - 1 = 2. Now the formula is targeting the month + 2. And if we drag that formula down 1000 rows, it only changes to Column(C1000)-1, which is still 3 - 1 = 2.

[MrDetermination]

Got it! Thank you so much!

One more question... why did the simple sum formula for S break?

[daffodil11]

Because empty cells have a value of zero, but cells with "" in them do not. Excel looks at it like "bob" + 10 and just comes up with an error.

That's why I asked you to change S5 = sumif(P5:R5,">0") and copy downwards. This will skip the artificial blanks we created and only add the numbers.

[MrDetermination]

Again! Thanks so much!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[MrDetermination]

Done and done. Thanks Arlu.