Find month when criteria has been met (sum of project cost)

[chet645]

All, wondering if someone can help me with a formula. I need a formula that will calculate the month when total project cost has been spent (e.g. 500 in the table below). In the table below, the correct month is 5.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD]

10​

[/TD]
[TD]

11​

[/TD]
[/TR]
[TR]
[TD]

Project Cost​

[/TD]
[TD]

​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[/TR]
[TR]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[/TR]
[TR]
[TD]

Total Project Cost​

[/TD]
[TD]

500​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[/TR]
[TR]
[TD]

Month of Completion​

[/TD]
[TD]

5​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance for your help.

Chet

 

[Giordano Bruno]

Thanks Peter. Curiouser and curiouser. I tested my workbook on two other computers and each time, when it loads, both formulas show as arrays. When I change one to a normal formula, it doesn't work. When I put the same thumb drive back into my desktop computer, the first formula shows as normal (as it was saved). When I press F2 and Enter, it enters as a normal formula and gives the correct results. There are clearly dark forces at work here!

 

[Peter_SSs]

There are clearly dark forces at work here!

It seems that way!

 

[chet645]

See if this is what you want. I have added a few examples for checking.
Formula in B5 copied across.

Cost Spent

A

B

C

D

E

F

G

H

I

J

K

L

M

Month

Project Cost

Total Project Cost

Month of Completion

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:156px;"><col style="width:40px;"><col style="width:40px;"><col style="width:47px;"><col style="width:40px;"><col style="width:40px;"><col style="width:40px;"><col style="width:33px;"><col style="width:40px;"><col style="width:40px;"><col style="width:26px;"><col style="width:33px;"><col style="width:33px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]780[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]8[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B5 =IFERROR(MATCH(TRUE,INDEX(SUMIF(OFFSET($C$2,,,1,COLUMN($C2:$M2)-COLUMN($C2)+1),"<>")>=B4,,0),0),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you very much, Peter. This is a beautiful solution. Quick follow up question. My post was a simplified version of a more complex spreadsheet. The month row in my actual model is at the very top of the worksheet (70+ rows above), and I have given it a named range "INDEX_MONTH." If I wanted to modify your formula to reference the named range, is there an easy way to do it? Also, one other clarification, the month # in my spreadsheet is actually a month, not a month #. I am somewhat of a novice, but I believe your formula returns a column #, and I need it to pull an actual date? Again, I really appreciate your help.

Regards,

Chet

 

[Peter_SSs]

Try this change for the B5 formula in my layout.

=IFERROR(INDEX(INDEX_MONTH,MATCH(TRUE,INDEX(SUMIF(OFFSET($[size=+1]B[/size]$2,,,1,COLUMN($C2:$M2)-COLUMN($C2)+1),"<>")>=B4,,0),0)),"")

The red [size=+1]B[/size] should be whatever is the first column in your INDEX_MONTH named range. So if that named range started in column C then make that red column ref a C

 

[chet645]

Try this change for the B5 formula in my layout.

=IFERROR(INDEX(INDEX_MONTH,MATCH(TRUE,INDEX(SUMIF(OFFSET($[SIZE=+1]B[/SIZE]$2,,,1,COLUMN($C2:$M2)-COLUMN($C2)+1),"<>")>=B4,,0),0)),"")

The red [SIZE=+1]B[/SIZE] should be whatever is the first column in your INDEX_MONTH named range. So if that named range started in column C then make that red column ref a C

Once again. Thanks, Peter! I will give it a try. Really appreciate your help.

Kindest Regards,
Chet

 

[Tom.Jones]

There are clearly dark forces at work here!

It seems that way!

In Office 365 Insider you no longer need to enter a matrix formula with CSE. A simple ENTER is enough.

 

[Giordano Bruno]

Hi Tom. Thanks so much for that. I'm very grateful for your post. Yes, that's the difference. I shall clearly have to be careful how I use this computer if I ever distribute workbooks. Nice to know that neither I nor my computer are going mad.

 

[Peter_SSs]

In Office 365 Insider you no longer need to enter a matrix formula with CSE. A simple ENTER is enough.

Ah, I'm not in the Insiders program and thought the simple Enter only applied to the 'new' array formulas being released there.