• 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.

Calculating YTD values

M

Mahantesh

Member
Hi Team,

Attached is the playbook,

I want a formula where i can find out YTD value of (eg. Type-6) when Month is selected frm F5 cell.

If "June" then it shud give total TYPE-6 values from Jan+Feb+Mar+Apr+May+jun all three.

Please help! I need urgently for my Dashboard

I have Attached the file!
cleardot.gif
 

Attachments

  • Excel_Formula_Problem.xlsx
    13 KB · Views: 8
See attached.

Make sure that you have G6 value exactly matching those in Column A.
 

Attachments

  • Excel_Formula_Problem_S.xlsx
    13.8 KB · Views: 11
Hi Mahantesh,

I would use Sumifs and something like this. The type 6 is missing a '-' in cell F6.

=SUMIFS($B$3:$B$40;$A$3:$A$40;$F$6;$D$3:$D$40;$F$5)

Hope this solved the issue.

Kind regards,
A!
 
Neat solution Deepak!

I never knew that =Month(1&"mmm") Converts "mmm" to numeric month value.
 
Hi to all,
Here is one more (array)

=SUM(IF(A3:A40=F6,IF(TEXT(1&D3:D40,"mm")<=TEXT(1&F5,"mm"),B3:B40)))
with Ctrl+Shift+Enter

or non CSE:
=SUMPRODUCT((A3:A40=F6)*(TEXT(1&D3:D40,"mm")<=TEXT(1&F5,"mm"))*(B3:B40))

Regards,
 
Last edited:
Simplified @Khalid NGO Approach...

CSE
=SUM((A3:A40=F6)*(TEXT(1&D3:D40,"mm")<=TEXT(1&F5,"mm"))*(B3:B40))


Non array..

=SUM(INDEX((A3:A40=F6)*(TEXT(1&D3:D40,"mm")<=TEXT(1&F5,"mm"))*(B3:B40),,))
 
Hello Guys,

OMG... You guys are Rock Stars in Excel. Thank you very much for that formula. If I get any appreciation from client then I will post that here. And that one belongs to you guys... Real Excel Gurus.

Just wish me luck... AMEN
 
You must log in or register to reply here.
Back
Top