• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for updating numbers

V

Veeru106

Member
Hi,


I am looking for a formula which update my total cost numbers in cell B18, for that month which we update in cell A17.


Currently we have selected Jun so it shd give me 7449 and when I change it to July and so forth, it shd update number accordingly.


Thanks very much for your assistance on this.
 
Thanks AliGW...sorry i had not specified earlier but i want Year till date numbers...means total of numbers , till the month which is in A17, so my answers shd be 36228 (sum of total cost from jan to june)..
 
This requires a simple lookup only.
= INDEX( totalCost, MATCH( selectedMonth, headingMonth, 0 ) )

Better would be to convert your month headings to proper dates (01/01/2018 etc). They can be displayed using the number format "mmm". The headings themselves can be used as the validation list to ensure consistency and, letting the selected month have the same number format, the dropdown will show the month rather than the serial number for the date.

Given a sorted list to search, the formula could then reduce to
= LOOKUP( selectedMonth, headingMonth, totalCost )
 

Attachments

  • HLookup.xlsx
    12.2 KB · Views: 0
That changes everything.
= SUM( INDEX( totalCost, 1 ) : INDEX( totalCost, MATCH( selectedMonth, headingMonth, 0 )
would return the running total you require but, suddenly, Ali's SUMIF is looking more attractive.
= SUMIFS( totalCost, headingMonth, "<="&selectedMonth )
 

Attachments

  • HLookup.xlsx
    12.2 KB · Views: 4
Veeru106 said:
Thanks AliGW...sorry i had not specified earlier but i want Year till date numbers...means total of numbers , till the month which is in A17, so my answers shd be 36228 (sum of total cost from jan to june)..
Click to expand...

That's completely different! You stated 7449, which is not a year-to-date figure at all!!!

Try this instead:

=SUM(OFFSET($B10,0,0,1,MATCH(A13,B2:M2,0)))
 
Better would be to convert your month headings to proper dates (01/01/2018 etc).
Click to expand...

I can't really see a need for this in this case. It's good advice and might be useful for other calculations, but what is required here can be done easily enough without messing around with the data.
 
Ali
what is required here can be done easily enough without messing around with the data
Click to expand...
True -- but then the problem changed. It is a matter of mind-set; I tend to go for capturing as much of the structure of the business application as I can (within reason) and treat the problem, as defined, as a specific instance.

In the present case, the additional set-up costs permits the use of
headingMonth <= selectedMonth
despite the fact that
Jan <= Mar
would evaluate to FALSE.

I could also extend the table to include the January from the following year. Most likely that will be on the next sheet but who knows?
 
You must log in or register to reply here.
Back
Top