Profit and Loss Calculation

[Nicholas Chew]

Hi all,

I am creating a spreadsheet which show the profits of the month and year.

I having troubles doing this spreadsheet. Help me someone.

I have a dropdown list, Jan to Dec and Year 2013. I can choose any month or year then the page will show me the profits for the particular month or year. How do i do it?

Nicholas

 

[CharlesChuckieCharles]

Look at SUMIFS

 

[mrmmickle1]

You may want to do a combination of a SUMIFS like CharlesChuckieCharles mentioned, a dropdown list and a worksheet event.

[COLOR=#0000ff]Private Sub [/COLOR]Worksheet_Change([COLOR=#0000ff]ByVal[/COLOR] Target[COLOR=#0000ff] As[/COLOR] Range)
    
    Application.EnableEvents = [COLOR=#0000ff]False [/COLOR][COLOR=#008000]'Disable Events otherwise when we change the values the Event will Re-Trigger[/COLOR]
    Application.ScreenUpdating = [COLOR=#0000ff]False[/COLOR] [COLOR=#008000]'Turn Off ScreenUpdating[/COLOR]
    
    [COLOR=#0000ff]If [/COLOR]Target.Column = 2 [COLOR=#0000ff]Then[/COLOR]  [COLOR=#008000] 'If Target Column is B Then run the below procedure[/COLOR]
    
            Range("B2").Value = ""  [B][COLOR=#ff0000]'PUT YOUR SUMIFS FORMULA HERE[/COLOR][/B]
             
[COLOR=#0000ff]    End If[/COLOR]
   
    Application.EnableEvents = [COLOR=#0000ff]True[/COLOR][COLOR=#008000] 'Enable Events[/COLOR]
    Application.ScreenUpdating = [COLOR=#0000ff]True[/COLOR][COLOR=#008000] 'Turn On  ScreenUpdating[/COLOR]
    
[COLOR=#0000ff]End Sub[/COLOR]