# Challenging Formula Issue

A

#### Alex

I have been having serious trouble trying to sort this out. It is a little
complicated and I will score major points with my supervisor if I can sort
this out so here goes...

I have four sheets of data that represent various international billing
disputes. They all contain the date that the dispute was filed in column B
and the issue type, which is one of 14 options, in column D. I would like to
summarize the data in a table on a fifth sheet.

The table needs to be organized by columns representing each month's
disputes, and rows identifying the type of billing dispute. So, for example,
there is a March 2009 column which contains all March 09 disputes from the 4
spreadsheets. Also, theres a row so we can see all Misquotes (an issue type)
from the entire timeframe we have recorded data. This way we can pinpoint the
number of a particular type of issue in any given month.

At present, I am able to pull information from two spreadsheets into the
first cell (C7) using the formula:

=SUM(--('CLOSED INTERNATIONAL DATA'!\$B\$2:\$B\$1551<=C\$3),--('CLOSED
INTERNATIONAL DATA'!\$B\$2:\$B\$1551>=C\$2),--('CLOSED INTERNATIONAL
DATA'!\$D\$2:\$D\$1551=\$B8), --('International
Data'!\$B\$2:\$B\$1551<=C\$3),--('International
Data'!\$B\$2:\$B\$1551>=C\$2),--('International Data'!\$D\$2:\$D\$1551=\$B8))

Where CLOSED INTERNATIONAL DATA and International Data are two of the four
other sheets; C2 and C3 are the beginning dates of the month respectively
that is represented in column C; and B8 is the cell that indicates the issue
type.

The problem arises when I try to add the components from the third sheet to
the formula in cell C7 (and every subsequent cell). Is there a limitation on
foreign sheet references in one formula? Does anyone know any way to
circumvent this obstacle?

T

#### Tom-S

Assuming the data all lies within the same year, say on your 5th sheet you
have some column headers in row 1: A1 is Issue Type, B1 is Jan-2009, C1 is
Feb-2009, etc up to M1 is Dec-2009 (with B1 to M1 formatted as mmm-yyyy).
Then A3:A16 is filled with your 14 issue types.

You will then fill a table B3:M16 with formulas to calculate the number of
issue types raised in each month. In cell B3 is the formula:

=SUMPRODUCT((MONTH('Sheet 1'!\$B\$2:\$B\$1551)=MONTH(B\$2))*('Sheet
1'!\$C\$2:\$C\$1551=\$A3)) + SUMPRODUCT((MONTH('Sheet
2'!\$B\$2:\$B\$1551)=MONTH(B\$2))*('Sheet 2'!\$C\$2:\$C\$1551=\$A3)) +
SUMPRODUCT((MONTH('Sheet 3'!\$B\$2:\$B\$1551)=MONTH(B\$2))*('Sheet
3'!\$C\$2:\$C\$1551=\$A3)) + SUMPRODUCT((MONTH('Sheet
4'!\$B\$2:\$B\$1551)=MONTH(B\$2))*('Sheet 4'!\$C\$2:\$C\$1551=\$A3))

Drag fill the formula to the rest of the table and it should collate your
year's data.

Gets a bit more complicated if data lies across multiple years - post again
if it does.

Regards,

Tom