Checking & matching values from 1 tab and placing result in another

**Shaz_79** · 01-04-2012, 06:25 PM

Please can someone assist.

I have a spreadsheet with various tabs per month. Each tab contain some data, I am specifically interested in 2 columns (Column I and Column M). As an example, in the tab Oct, I want to see how many times the name 'Ade A' appears in Column I and how many times 'L' appears in Column M. The count/sum of this I want placed in the tab 'Totals YTD' in cell D7. Similarly I want to do the same where the combination is 'Ade A' in column I and 'M' in column M, this value should be placed in tab 'Totals YTD' in cell E7, and then for 'Ade A' and 'H' in cell F7. I then want to repeat this for all the people in the file.

Additionally, in the 'Totals YTD' tab I want cells D7, E7 and F7 to count the data as above from each month, thus eventually cell D7 will have the data for 'Ade A' for each month as a total value.

Can someone assist me with a formula on how to do this? Unfortunately, a pivot table isn't going to work for me as the data in the tabs is updated regularly, thus I would repeatedly need to create new pivot tables, thus I'm looking for a formula as then the data will auto-refresh.

I have attached a spreadsheet which shows what I am trying to do.

Thanks in advance.
shaz_79

**Haseeb Avarakkan** · 01-04-2012, 08:17 PM

Firstly define a name as Sheets, on refers to use;

=TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")

Then, try this in D6, copy down.

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&Sheets&"'!I2"),ROW(A$1:A$1000)-ROW(A$1),))="L"),--(T(OFFSET(INDIRECT("'"&Sheets&"'!M2"),ROW(A$1:A$1000)-ROW(A$1),))=A6))

Adjust the range for others. Note: INDIRECT/OFFSET both are volatile.