Hi

With table on sheet Data (row 1 are headers), on fly:
=SUMPRODUCT(--(Status="Perm"),--(LEFT(Location,2)*1=Criteria),Head)
where
Status - a named range defined as
INDEX(DataTable,,MATCH("STATUS",DataHead,0))
Location - a named range defined as
INDEX(DataTable,,MATCH("LOCATION_CODE",DataHead,0) )
Head - a named range defined as
INDEX(DataTable,,MATCH("HEAD_COUNT",DataHead,0))
, where
DataTable - a named range defined as
=OFFSET(Data!$A$1,1,,COUNTA(Data!$A:$A)-1,COUNTA(DataHead))
DataHead - a named range defined as
=OFFSET(Data!$A$1,,,1,COUNTA(Data!$1:$1)-1)


Arvi Laanemets


"Ronny Hamida" wrote in message
...
Hello again Excel Wizards!

Last year, I worked pretty hard on creating VBA to split my data and
organize it so that it was 1) uniform and 2) organized for the process of
picking at it just to get the sum of numbers in one column... So now I'm
wondering if that was overkill and if it could be done with an IF
function,
but all I keep thinking about is that it would be too long to type into a
cell. Sorry for the length, but here's what I'm going for:

Currently, I'm extracting data from a third-party program into Excel. The
columns, however are not always in the same place which is why I had
chosen
to write a VBA macro to extract those select few columns and place them
into
the same order on another sheet for the purpose of searching through it
and
summing up the data if it meets my criteria. Here's a sample of what it
(the
seperate file) looks like (I have used commas to seperate fields as
spacing
could be a problem on the screen):

EMPLOYEE,HEAD_COUNT,LOCATION_CODE,DIVISION_CODE,ST ATUS,etc...
Ronny,1.0,99990001,99,Temp,...
Jennifer,1.0,98990001,98,Temp,...
Roger,1.0,99990001,99,Perm,...

Here's what I'd like to do in another Excel file:

I want an IF function (SUM IF maybe, but not VBA/Macro code) that will
first
check the STATUS column (in the data file) for Temp or Perm, and if it's
Perm
I would like it to tally up the numbers in the HEAD_COUNT field if the
first
four numbers in the LOCATION_CODE matches my criteria. This would
basically
give me a total number (I have to use a SUM of the HEAD_COUNT because it
could be a 0.5) for a location (signified by the first two digits of the
LOCATION_CODE) if and only if they are a Perm employee.

The macro (that I'm using now) splits the data by Location, removed all
Temp
employee records, and sums up the HEAD_COUNT for each location into a
subtotal or sorts. It also names/defines each subtotal with the name of
"LocationXXXX" where "XXXX" is the 4 digit location number.

Then in another document (my totals document), I have calls to the already
split-up/organized files for the subtotal numbers using the named/defined
fields.

I hope this all makes sense! As I said, if there's no way to perform all
of
these things within an IF or SUMIF function, it's no big deal since I have
a
working Macro.

Thank you!

Ronny