Vlook with 2 conditions and sum if the condition is true

[hema_mathi76]

I have 2 excel sheets.


1) Sheet1..... Summary sheet

2) Sheet2..... contains the raw data


Sheet`

A B C

Month Chq_pending To_obtain_Sign

May-13 4 3


Sheet2

A B

Month Comments

May-13 Chq_pending

May-13 To_obtain_Sign

May-13 To_obtain_Sign

May-13 Chq_pending

May_13 Chq_pending

May_13 To_obtain_Sign

May_13 Chq_pending

`


Is this possible by using Vlook!

 

[NARAYANK991]

Hi Hema ,


This is possible with COUNTIFS , which is available only in Excel 2007 and later ; if you have Excel 2003 or earlier versions , then a different formula will have to be used.


=COUNTIFS(Sheet2!$A$2:$A$8,$A2,Sheet2!$B$2:$B$8,B$1) in cell B2 on Sheet1


=COUNTIFS(Sheet2!$A$2:$A$8,$A2,Sheet2!$B$2:$B$8,C$1) in cell C2 on Sheet1.


I have assumed the following ranges for your data :


A1:B8 on Sheet2 , with row 1 for the headers


A1:C2 on Sheet1 , with row 1 for the headers


Narayan

 

[Sam Mathai Chacko]

Keeping the same assumptions as Narayan's, you could make use of the power of pivot tables. Select your data from the two columns, including the column headers, and in the Row label, drag the Months column, in the Column label, drag the Comments field, and in the Values section, drag either the months or comments. By default, it should take Count as the aggregation, but if it doesn't, you can change it. The advantage of a pivot table is that you only need to update the data source for the pivot table, and don't have to drag formulas as your data keeps on increasing. If you are the lazy kind, you could use a dynamic named range to take care of that too, but since that would be digressing of topic, I leave it to this.