new user: problems with array formula

  • Thread starter Thread starter jeffceth
  • Start date Start date
J

jeffceth

Hi!
I'm not very advanced with excel. This is what I'm trying to do:

I have a set-up like this:

sheet 1:
(ordered text values, one of each)

A
B
C
D
E
F
G

sheet 2:
(first column-ordered text values zero to lots of each)
(second column-either a number from 1-4 or a blank cell)

A 1
A
A 3
C 2
D
E 4
F 4
F 4
G 2

I want to create five more columns on the first sheet, where the first
column lists the number of rows from sheet 2 that have a '1' for each
respective letter, the second column lists the number of rows from
sheet 2 that have a '2' for each respective letter, and so on. In this
specific example, that would change the first sheet to:

A 1 0 1 0 1
B 0 0 0 0 0
C 0 1 0 0 0
D 0 0 0 0 1
E 0 0 0 1 0
F 0 0 0 2 0
G 0 1 0 0 0


In actuality, both sheets are somewhere around 2000 rows in length, so
I'm looking for something I can create in the first row and then draq
down to extend it automatically to the rest.

Profuse thanks in advance to anyone who can help me.

sincerely,
jeffceth
 
Hi try the following in cell B1:
=--(SUMPRODUCT(('Sheet 1'!$A$1:$A:$9999=$A1)*('Sheet
1'!$B$1:$B:$9999=COLUMN()-1))>0)
copy this for all rows/columns

HTH
Frank
 
Hi!
I don't quite understand exactly what that formula's doing, but i
doesn't have the right results: it comes up with only zeroes(I di
adjust the sheet names, columns, etc. to fit my spreadheet, so that'
not it). Let me be a bit more specific. The text cells in the firs
column of each sheet represent canadian postal codes. The first shee
contains a lot of general information sorted by postal code, and th
second sheet is a long list of people, also sorted by postal code, bu
some postal codes have no people, and there can be several people wit
the same postal code. Each person has a status value 1-4 or didn'
provide any info(blank cell). On the first sheet I want to list th
total for each status category per postal code. So as far as the ne
columns I want to create on the first sheet, the first column woul
specify for each postal code how many people from the second sheet ha
that same postal code and have a status of '1'. This is the formula
tried to use before for the first column(I have 1268 rows):

{=SUM(IF(AND('Sheet2'!$A$1:$A$1268=A1,'Sheet2'!$B$1:$B$1268=1),'Sheet2'!$B$1:$B$1268))}

but it didn't work, though I'm not sure why.

sincerely,
jeffcet
 
Hi
If you like mail me your spreadsheet and I'll create an example for the
formula. In your case you have to replace the COLUMN() part in the
function with a different reference

Frank
 
Thanks so much for your help. I ended up being able to use

{=SUM(IF(Sheet2!$A$1:$A$1268='Sheet1'!$A1,IF('Sheet2'!$B$1:$B$1268=1,1)))}

which extends three-dimensionally very well to do all the column
simply by changing the first '1' to a 2,3,etc.

sincerely,
jeffcet
 
Hi
I would change this to a SUMPRODUCT formula (as this won't need to be
array entered and is IMHO easier to read). For your example
=SUMPRODUCT((Sheet2!$A$1:$A$1268='Sheet1'!$A1)*('Sheet2'!$B$1:$B$1268=1
))
should return the same result


Frank
 
Back
Top