Search code examples
excelsumexcel-formulapivot-tableexcel-2013

Need to calculate SUM of certain values in one column depending on a value in different column in same row

I have data structure like following

No      Date       Name Vehicle     Finance
1001    01/04/2015  abc Activa       HDFC
1002    02/04/2015  abc Activa 125
1003    02/04/2015  abc Activa 3G    HDFC
1004    02/04/2015  abc Shine
1005    01/05/2015  abc Activa 3G
1006    01/05/2015  abc Dream Yuga
1007    02/05/2015  abc Unicorn 160

Now what I need is count of each vehicle for a certain month, say April. I was trying to do it something like this 

=IF(MONTH('Sales Register'!C3)=4,COUNT('Sales Register'!A3:A5000),0)

But clearly it isn't the right way. How it can be done? Also if there is more complexity like I want to sum no of Activas which are financed for a specific month by HDFC, how will it be done?

Solution

  • Assuming No is in A1, select A1:E8, INSERT > Tables, PivotTable. Click Existing Worksheet and for Location: G1, OK. Drag Date from Choose fields to add to report: to ROWS, drag Vehicle underneath Date. Drag Finance to COLUMNS and Name into Σ VALUES.

    PIVOTTABLE TOOLS > DESIGN > Layout Report Layout - Show in Tabular Form. Right-click ColumnG in the PT and uncheck Subtotal "Date". Right-click again and click Group... . Select Months and Years, OK.

    Click the down arrow next to Vehicle, Label Filters, Contains..., enter Activa, OK. Click the down arrow next to Date, click the tick next to (Select all), and the empty box next to Apr, OK.

    Result should look something like the following, though may depend slightly on Excel version and any remembered settings:

    SO28761768 example