References to Ranges

R

Raymond Wood

I have the following data in Sheet 1:

A B
1 St. David's - Unit 1 £500
2 St. David's - Unit 2 £600
3 St. David's - Unit 3 £300
4 Dalkeith - Unit 1 £200
5 Dalkeith - Unit 2 £300
6 SEN - Unit 1 £700
7 SEN - Unit 2 £700

Now in Sheet 2, I have the following data




A B C D E
1 1-Feb 2-Feb 3-Feb 4-Feb . . . . .
2 St. David's ab be bd cd
3 St. David's abc me
4 St. David's bdc
5 Dalkeith f
6 Dalkeith me f
7 SEN n
8 SEN xxx m
...
...
...
...

(This is a simplified version - the labels in column A can
only be St. David's - Unit "x", Dalkeith - Unit "x" or
SEN - Unit "x", but there are 500 entries in this column).


In Sheet 3, I have a formula in cell C9 as follows:

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'!
$B$2,,,3,$E$4)))=FALSE)*('Sheet1'!$B$1:$B$3))

where B9 contains "a".

This formula gets copied down for "b", "c", etc

$E$4 contains the number of days for the given month (i.e.
for February it will contain 29 this year), so what the
above formula effectively does is count the number of
times "a" appears in the "last day of the month column"
for all Units within St. David's.

In Cell D9, I have the formula

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'!
$B$5,,,2,$E$4)))=FALSE)*('Sheet1'!$B$4:$B$5))

which calculates the number of times "a" appears in the
last day of the month column for all Units within Dalkeith.

A similar formula is used for SEN.

However, the problem I have is that the number of Units
within each school is still constantly changing, and
because all schools are listed on a consecutive vertical
list, the "SUMPRODUCT" formula loses its value (due to the
OFFSET function), as it will pick up occurences of "a"
from the wrong set of Units.

Therefore, I could do a COUNTIF to count the number of
times each of the Schools appears within the list. (in
the case above, St. David's appear 3 times, Dalkeith and
SEN twice).

I would then know:

the starting position for the OFFSET
the length/height of the OFFSET search
the search range in sheet 1

But how do I translate these into cell references??

I.e. given the example above,

For Dalkeith, I would want the starting position of the
OFFSET to be B5 (since this is B2 + 3 :the number of Units
within St. David's)

AND

the length of the OFFSET search woould be 2 (the number of
Units within Dalkeith)

the search range in sheet 1 would be 'Sheet1'$B$4:$B$5
(where $B$4 is found by $B$1 plus 3 = $B$4 :the 3 being
the number of Units within St. David's, and the $B$5 is
found by $B$1 plus 3 plus (2-1) = $B$5 :the 3 being the
number of Units within St. David's and the (2-1) the extra
required to account for the Units within Dalkeith).


I realise this is an extremely long post and it may not
make sense, but there may be a really easy way to solve
this.
..
 
G

Guest

Raymond
I played around with this after seeing your post from yesterday. I didn't have a chance to get it working, but have you considered using a lookup function like MATCH instead of OFFSET. You could possibly use some combination of INDIRECT, ADDRESS, nad MATCH to specify the ranges

If I have some more time I'l play around with it some more

Good Luck
Mark Graesse
(e-mail address removed)

----- Raymond Wood wrote: ----

I have the following data in Sheet 1

A
1 St. David's - Unit 1 £50
2 St. David's - Unit 2 £60
3 St. David's - Unit 3 £30
4 Dalkeith - Unit 1 £20
5 Dalkeith - Unit 2 £30
6 SEN - Unit 1 £70
7 SEN - Unit 2 £70

Now in Sheet 2, I have the following dat




A B C D
1 1-Feb 2-Feb 3-Feb 4-Feb . . . . .
2 St. David's ab be bd c
3 St. David's abc me
4 St. David's bd
5 Dalkeith
6 Dalkeith me
7 SEN
8 SEN xxx
..
..
..
..

(This is a simplified version - the labels in column A can
only be St. David's - Unit "x", Dalkeith - Unit "x" or
SEN - Unit "x", but there are 500 entries in this column)


In Sheet 3, I have a formula in cell C9 as follows

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'
$B$2,,,3,$E$4)))=FALSE)*('Sheet1'!$B$1:$B$3)

where B9 contains "a"

This formula gets copied down for "b", "c", et

$E$4 contains the number of days for the given month (i.e.
for February it will contain 29 this year), so what the
above formula effectively does is count the number of
times "a" appears in the "last day of the month column"
for all Units within St. David's

In Cell D9, I have the formul

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'
$B$5,,,2,$E$4)))=FALSE)*('Sheet1'!$B$4:$B$5)

which calculates the number of times "a" appears in the
last day of the month column for all Units within Dalkeith

A similar formula is used for SEN

However, the problem I have is that the number of Units
within each school is still constantly changing, and
because all schools are listed on a consecutive vertical
list, the "SUMPRODUCT" formula loses its value (due to the
OFFSET function), as it will pick up occurences of "a"
from the wrong set of Units

Therefore, I could do a COUNTIF to count the number of
times each of the Schools appears within the list. (in
the case above, St. David's appear 3 times, Dalkeith and
SEN twice)

I would then know

the starting position for the OFFSE
the length/height of the OFFSET searc
the search range in sheet

But how do I translate these into cell references?

I.e. given the example above,

For Dalkeith, I would want the starting position of the
OFFSET to be B5 (since this is B2 + 3 :the number of Units
within St. David's

AN

the length of the OFFSET search woould be 2 (the number of
Units within Dalkeith

the search range in sheet 1 would be 'Sheet1'$B$4:$B$5
(where $B$4 is found by $B$1 plus 3 = $B$4 :the 3 being
the number of Units within St. David's, and the $B$5 is
found by $B$1 plus 3 plus (2-1) = $B$5 :the 3 being the
number of Units within St. David's and the (2-1) the extra
required to account for the Units within Dalkeith)


I realise this is an extremely long post and it may not
make sense, but there may be a really easy way to solve
this
 
G

Guest

Raymond
Okay, I'm getting there

=SUMPRODUCT((ISERROR(FIND(C9,OFFSET(INDIRECT(ADDRESS(MATCH(D8,Sheet2!A1:A1000,0),2,,,"Sheet2")),,,COUNTIF(Sheet2!A1:A100,D8),$F$4)))=FALSE)*(Sheet1!$B$1:$B$3)

INDIRECT(ADDRESS(MATCH(D8,Sheet2!A1:A1000,0),2,,,"Sheet2")
Locates the first cell in column B on sheet2, adjacent to the value you enter in D8 on sheet3, You would need to enter the school name in D8 which I assume is the header cell for that column in your table

COUNTIF(Sheet2!A1:A100,D8
Counts the occurences of the school name to set the number of rows in your offset range

(Sheet1!$B$1:$B$3
You still need to update this manually. I am working on some way to automate the update of this component, and will repost if I come up with something

A couple questions. It seems that your formula is counting the number of times "a" occures in the entire month, not just the last day of the month. Is this what you want

I assume, from your formula, taht the intent is to count the number of "a"s in the first row of a school on sheet2, and multiply it by the value next to the Unit 1 of that school on sheet1, then do this for each row and unit and add them all up. Does this seem correct

Regards
Mark Graesse
(e-mail address removed)



----- Raymond Wood wrote: ----

I have the following data in Sheet 1

A
1 St. David's - Unit 1 £50
2 St. David's - Unit 2 £60
3 St. David's - Unit 3 £30
4 Dalkeith - Unit 1 £20
5 Dalkeith - Unit 2 £30
6 SEN - Unit 1 £70
7 SEN - Unit 2 £70

Now in Sheet 2, I have the following dat




A B C D
1 1-Feb 2-Feb 3-Feb 4-Feb . . . . .
2 St. David's ab be bd c
3 St. David's abc me
4 St. David's bd
5 Dalkeith
6 Dalkeith me
7 SEN
8 SEN xxx
..
..
..
..

(This is a simplified version - the labels in column A can
only be St. David's - Unit "x", Dalkeith - Unit "x" or
SEN - Unit "x", but there are 500 entries in this column)


In Sheet 3, I have a formula in cell C9 as follows

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'
$B$2,,,3,$E$4)))=FALSE)*('Sheet1'!$B$1:$B$3)

where B9 contains "a"

This formula gets copied down for "b", "c", et

$E$4 contains the number of days for the given month (i.e.
for February it will contain 29 this year), so what the
above formula effectively does is count the number of
times "a" appears in the "last day of the month column"
for all Units within St. David's

In Cell D9, I have the formul

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'
$B$5,,,2,$E$4)))=FALSE)*('Sheet1'!$B$4:$B$5)

which calculates the number of times "a" appears in the
last day of the month column for all Units within Dalkeith

A similar formula is used for SEN

However, the problem I have is that the number of Units
within each school is still constantly changing, and
because all schools are listed on a consecutive vertical
list, the "SUMPRODUCT" formula loses its value (due to the
OFFSET function), as it will pick up occurences of "a"
from the wrong set of Units

Therefore, I could do a COUNTIF to count the number of
times each of the Schools appears within the list. (in
the case above, St. David's appear 3 times, Dalkeith and
SEN twice)

I would then know

the starting position for the OFFSE
the length/height of the OFFSET searc
the search range in sheet

But how do I translate these into cell references?

I.e. given the example above,

For Dalkeith, I would want the starting position of the
OFFSET to be B5 (since this is B2 + 3 :the number of Units
within St. David's)

AND

the length of the OFFSET search woould be 2 (the number of
Units within Dalkeith)

the search range in sheet 1 would be 'Sheet1'$B$4:$B$5
(where $B$4 is found by $B$1 plus 3 = $B$4 :the 3 being
the number of Units within St. David's, and the $B$5 is
found by $B$1 plus 3 plus (2-1) = $B$5 :the 3 being the
number of Units within St. David's and the (2-1) the extra
required to account for the Units within Dalkeith).


I realise this is an extremely long post and it may not
make sense, but there may be a really easy way to solve
this.
..
 
G

Guest

Raymond
Okay, after a little more work I came up with

=SUMPRODUCT((ISERROR(FIND($B9,OFFSET(INDIRECT(ADDRESS(MATCH(C$8,Sheet2!$A$1:$A$1001,0),2,,,"Sheet2")),,,COUNTIF(Sheet2!$A$1:$A$1001,C$8),$E$4)))=FALSE)*(INDIRECT(ADDRESS(MATCH(C$8&"*",Sheet1!$A$1:$A$1001,0),2,,,"Sheet1")&":"&ADDRESS(MATCH(C$8&"*",Sheet1!$A$1:$A$1001,0)+COUNTIF(Sheet1!$A$1:$A$1001,C$8&"*")-1,2)))

In this formula I replaced (Sheet1!$B$1:$B$3)) with
(INDIRECT(ADDRESS(MATCH(C$8&"*",Sheet1!$A$1:$A$1001,0),2,,,"Sheet1")&":"&ADDRESS(MATCH(C$8&"*",Sheet1!$A$1:$A$1001,0)+COUNTIF(Sheet1!$A$1:$A$1001,C$8&"*")-1,2))

The first ADDRESS formula is based on a match with the school name in sheet1, regardless of the unit number. The second ADDRESS adds the count of the school name to the row number and subtracts 1 to get the final row number

The above formula has all the right absolute and relative references so you can paste it into sheet3!C9 and then fill right and down

I assumed that if you add a row to sheet 2 you will also add a corresponding row to sheet 1

FYI, the ADDRESS function creates a text value, the INDIRECT function converts it into a reference value

If you have any trouble just post back

Good Luck
Mark Graesse

----- Raymond Wood wrote: ----

I have the following data in Sheet 1

A
1 St. David's - Unit 1 £50
2 St. David's - Unit 2 £60
3 St. David's - Unit 3 £30
4 Dalkeith - Unit 1 £20
5 Dalkeith - Unit 2 £30
6 SEN - Unit 1 £70
7 SEN - Unit 2 £70

Now in Sheet 2, I have the following dat




A B C D
1 1-Feb 2-Feb 3-Feb 4-Feb . . . . .
2 St. David's ab be bd c
3 St. David's abc me
4 St. David's bd
5 Dalkeith
6 Dalkeith me
7 SEN
8 SEN xxx
..
..
..
..

(This is a simplified version - the labels in column A can
only be St. David's - Unit "x", Dalkeith - Unit "x" or
SEN - Unit "x", but there are 500 entries in this column)


In Sheet 3, I have a formula in cell C9 as follows

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'
$B$2,,,3,$E$4)))=FALSE)*('Sheet1'!$B$1:$B$3)

where B9 contains "a"

This formula gets copied down for "b", "c", et

$E$4 contains the number of days for the given month (i.e.
for February it will contain 29 this year), so what the
above formula effectively does is count the number of
times "a" appears in the "last day of the month column"
for all Units within St. David's

In Cell D9, I have the formul

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'
$B$5,,,2,$E$4)))=FALSE)*('Sheet1'!$B$4:$B$5)

which calculates the number of times "a" appears in the
last day of the month column for all Units within Dalkeith

A similar formula is used for SEN

However, the problem I have is that the number of Units
within each school is still constantly changing, and
because all schools are listed on a consecutive vertical
list, the "SUMPRODUCT" formula loses its value (due to the
OFFSET function), as it will pick up occurences of "a"
from the wrong set of Units

Therefore, I could do a COUNTIF to count the number of
times each of the Schools appears within the list. (in
the case above, St. David's appear 3 times, Dalkeith and
SEN twice)

I would then know

the starting position for the OFFSE
the length/height of the OFFSET searc
the search range in sheet

But how do I translate these into cell references?

I.e. given the example above,

For Dalkeith, I would want the starting position of the
OFFSET to be B5 (since this is B2 + 3 :the number of Units
within St. David's)

AND

the length of the OFFSET search woould be 2 (the number of
Units within Dalkeith)

the search range in sheet 1 would be 'Sheet1'$B$4:$B$5
(where $B$4 is found by $B$1 plus 3 = $B$4 :the 3 being
the number of Units within St. David's, and the $B$5 is
found by $B$1 plus 3 plus (2-1) = $B$5 :the 3 being the
number of Units within St. David's and the (2-1) the extra
required to account for the Units within Dalkeith).


I realise this is an extremely long post and it may not
make sense, but there may be a really easy way to solve
this.
..
 
G

Guest

Hi Again Raymond
Can you tell that I found this to be an interesting problem

In this version I used an OFFSET function to select the range on sheet1

=SUMPRODUCT((ISERROR(FIND($B9,OFFSET(INDIRECT(ADDRESS(MATCH(C$8,Sheet2!$A$1:$A$1000,0),2,,,"Sheet2")),,,COUNTIF(Sheet2!$A$1:$A$1000,C$8),$E$4)))=FALSE)*OFFSET(INDIRECT(ADDRESS(MATCH(C$8&"*",Sheet1!$A$1:$A$1000,0),2,,,"Sheet1")),,,COUNTIF(Sheet2!$A$1:$A$1000,C$8),1)

So basically it breaks down to

=SUMPRODUCT

(ISERROR(FIND($B9
OFFSET
INDIRECT(ADDRESS(MATCH(C$8,Sheet2!$A$1:$A$1000,0),2,,,"Sheet2"))


COUNTIF(Sheet2!$A$1:$A$1000,C$8)
$E$4
))=FALSE


OFFSET
INDIRECT(ADDRESS(MATCH(C$8&"*",Sheet1!$A$1:$A$1000,0),2,,,"Sheet1"))


COUNTIF(Sheet2!$A$1:$A$1000,C$8)
1


The formula is still pretty messy but this provides a little more uniformity and removes 49 characters

Good Luck
Mark Graesse
(e-mail address removed)

----- Raymond Wood wrote: ----

I have the following data in Sheet 1

A
1 St. David's - Unit 1 £50
2 St. David's - Unit 2 £60
3 St. David's - Unit 3 £30
4 Dalkeith - Unit 1 £20
5 Dalkeith - Unit 2 £30
6 SEN - Unit 1 £70
7 SEN - Unit 2 £70

Now in Sheet 2, I have the following dat




A B C D
1 1-Feb 2-Feb 3-Feb 4-Feb . . . . .
2 St. David's ab be bd c
3 St. David's abc me
4 St. David's bd
5 Dalkeith
6 Dalkeith me
7 SEN
8 SEN xxx
..
..
..
..

(This is a simplified version - the labels in column A can
only be St. David's - Unit "x", Dalkeith - Unit "x" or
SEN - Unit "x", but there are 500 entries in this column)


In Sheet 3, I have a formula in cell C9 as follows

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'
$B$2,,,3,$E$4)))=FALSE)*('Sheet1'!$B$1:$B$3)

where B9 contains "a"

This formula gets copied down for "b", "c", et

$E$4 contains the number of days for the given month (i.e.
for February it will contain 29 this year), so what the
above formula effectively does is count the number of
times "a" appears in the "last day of the month column"
for all Units within St. David's

In Cell D9, I have the formul

=SUMPRODUCT((ISERROR(FIND(B9,OFFSET('Sheet2'
$B$5,,,2,$E$4)))=FALSE)*('Sheet1'!$B$4:$B$5)

which calculates the number of times "a" appears in the
last day of the month column for all Units within Dalkeith

A similar formula is used for SEN

However, the problem I have is that the number of Units
within each school is still constantly changing, and
because all schools are listed on a consecutive vertical
list, the "SUMPRODUCT" formula loses its value (due to the
OFFSET function), as it will pick up occurences of "a"
from the wrong set of Units

Therefore, I could do a COUNTIF to count the number of
times each of the Schools appears within the list. (in
the case above, St. David's appear 3 times, Dalkeith and
SEN twice)

I would then know

the starting position for the OFFSE
the length/height of the OFFSET searc
the search range in sheet

But how do I translate these into cell references?

I.e. given the example above,

For Dalkeith, I would want the starting position of the
OFFSET to be B5 (since this is B2 + 3 :the number of Units
within St. David's

AN

the length of the OFFSET search woould be 2 (the number of
Units within Dalkeith

the search range in sheet 1 would be 'Sheet1'$B$4:$B$5
(where $B$4 is found by $B$1 plus 3 = $B$4 :the 3 being
the number of Units within St. David's, and the $B$5 is
found by $B$1 plus 3 plus (2-1) = $B$5 :the 3 being the
number of Units within St. David's and the (2-1) the extra
required to account for the Units within Dalkeith).


I realise this is an extremely long post and it may not
make sense, but there may be a really easy way to solve
this.
..
 
H

Harlan Grove

...
...
INDIRECT(ADDRESS(MATCH(D8,Sheet2!A1:A1000,0),2,,,"Sheet2"))
Locates the first cell in column B on sheet2, adjacent to the value you
enter in D8 on sheet3, You would need to enter the school name in D8
which I assume is the header cell for that column in your table.
Click to expand...
...

This involves 3 function calls and 2 nested levels. In complex formulas this
could be too wasteful. It's more economical to use INDEX, which returns range
references when its first argument is a range reference.

INDEX(Sheet2!B1:B1000,MATCH(D8,Sheet2!A1:A1000,0))

Expressions involving INDIRECT(ADDRESS(...)) can *always* be rendered more
economically using INDEX, OFFSET or using R1C1-style textrefs in INDIRECT.
Indeed,

INDIRECT(ADDRESS(a,b,c,d,e))

can always be replaced by

INDIRECT("'"&e&"'!R"&a&"C"&b,0)
 
R

Raymond Wood

Hi Mark

Glad you found this problem interesting!!!

I have just got into work and am workingthrough your posts
to see if this works. May not egt it done today - it may
have to be resurrected after the weekend.

I took some notes on the points you made:

(1) The formula does indeed count the number of times "a"
occurs within the ENTIRE month, not just the last day. (I
have a separate formula which counts occurences of "a" on
the last day of the month, which if I can get this one
working I can adapt, as they are both similar).

(2) You are correct when you ascertain that the intent is
to count the number of "a"'s in the first row of a school
on sheet 2, then multiply it by the value next to the Unit
1 of that school on sheet1, then do this for each row and
Unit then add them all up.

(3) If I add a row to Sheet 2, then there WILL be a
corrsponding row added to Sheet 1.

I will let you know how I get on with your solutions.

Many Thanks,

Raymond Wood



-----Original Message-----
Hi Again Raymond,
Can you tell that I found this to be an interesting problem.

In this version I used an OFFSET function to select the range on sheet1:

=SUMPRODUCT((ISERROR(FIND($B9,OFFSET(INDIRECT(ADDRESS (MATCH(C$8,Sheet2!$A$1:$A$1000,0),2,,,"Sheet2")),,,COUNTIF
(Sheet2!$A$1:$A$1000,C$8),$E$4)))=FALSE)*OFFSET(INDIRECT
(ADDRESS(MATCH(C$8&"*",Sheet1!
$A$1:$A$1000,0),2,,,"Sheet1")),,,COUNTIF(Sheet2!
$A$1:$A$1000,C$8),1))

So basically it breaks down to:

=SUMPRODUCT(

(ISERROR(FIND($B9,
OFFSET(
INDIRECT(ADDRESS(MATCH(C$8,Sheet2! $A$1:$A$1000,0),2,,,"Sheet2")),
$A$1:$A$1000,0),2,,,"Sheet1")),
,
,
COUNTIF(Sheet2!$A$1:$A$1000,C$8),
1)
)

The formula is still pretty messy but this provides a
Click to expand...
little more uniformity and removes 49 characters.
 
G

Guest

Thanks Harlan
That definitely helps make a cleaner formula, and shortened it by 10 characters

I never really did like using ADDRESS nested inside INDIRECT, but I wasn't clear on how INDEX returns a reference. I usually have used it to return a single cell reference which evalutes to the value in the referenced cell, so the reference aspect was kind of transparent to me

Thanks again
Mark Graesse

Raymond
here's one more formula to confuse you with

=SUMPRODUCT((ISERROR(FIND($B9,OFFSET(INDEX(Sheet2!$B$1:$B$1000,MATCH(C$8,Sheet2!$A$1:$A$1000,0)),,,COUNTIF(Sheet2!$A$1:$A$1000,C$8),$E$4)))=FALSE)*OFFSET(INDEX(Sheet1!$B$1:$B$1000,MATCH(C$8&"*",Sheet1!$A$1:$A$1000,0)),,,COUNTIF(Sheet2!$A$1:$A$1000,C$8),1)

Regards
Mark Graesse

----- Harlan Grove wrote: ----

..
..
INDIRECT(ADDRESS(MATCH(D8,Sheet2!A1:A1000,0),2,,,"Sheet2")
Locates the first cell in column B on sheet2, adjacent to the value yo
enter in D8 on sheet3, You would need to enter the school name in D
which I assume is the header cell for that column in your table
Click to expand...
..

This involves 3 function calls and 2 nested levels. In complex formulas thi
could be too wasteful. It's more economical to use INDEX, which returns rang
references when its first argument is a range reference

INDEX(Sheet2!B1:B1000,MATCH(D8,Sheet2!A1:A1000,0)

Expressions involving INDIRECT(ADDRESS(...)) can *always* be rendered mor
economically using INDEX, OFFSET or using R1C1-style textrefs in INDIRECT
Indeed

INDIRECT(ADDRESS(a,b,c,d,e)

can always be replaced b

INDIRECT("'"&e&"'!R"&a&"C"&b,0

-
To top-post is human, to bottom-post and snip is sublime
 
H

Harlan Grove

...
...
here's one more formula to confuse you with.

=SUMPRODUCT((ISERROR(FIND($B9,OFFSET(INDEX(Sheet2!$B$1:$B$1000,
MATCH(C$8,Sheet2!$A$1:$A$1000,0)),,,COUNTIF(Sheet2!$A$1:$A$1000,C$8),$E$4)))
=FALSE)*OFFSET(INDEX(Sheet1!$B$1:$B$1000,MATCH(C$8&"*",Sheet1!$A$1:$A$1000,
0)),,,COUNTIF(Sheet2!$A$1:$A$1000,C$8),1))
Click to expand...
...

The (ISERROR(...)=FALSE) term could and should be rewritten as ISNUMBER(...).

Looking inside this call, FIND's first argument is a range, and you're comparing
it to another, dynamic range. Instead of checking FIND(R,A) for number values,
you could check COUNTIF(Rng,"*"&ArrayOrRng&"*") for positive values if matching
may be case-insensitive.

Next, OFFSET(INDEX(...),...) is redundant. The first one could be reduced to

OFFSET(Sheet2!$B$1,MATCH(C$8,Sheet2!$A$1:$A$1000,0)-1,1,
COUNTIF(Sheet2!$A$1:$A$1000,C$8),$E$4))

and the second one could be reduced to

OFFSET(Sheet1!$B$1,MATCH(C$8&"*",Sheet1!$A$1:$A$1000,0)-1,1,
COUNTIF(Sheet2!$A$1:$A$1000,C$8),1)

Putting this all together,

=SUMPRODUCT((COUNTIF($B9,"*"&OFFSET(Sheet2!$B$1,MATCH(C$8,Sheet2!$A$1:$A$1000,
0)-1,1,COUNTIF(Sheet2!$A$1:$A$1000,C$8),$E$4)&"*")>0)*OFFSET(Sheet1!$B$1,
MATCH(C$8&"*",Sheet1!$A$1:$A$1000,0)-1,1,COUNTIF(Sheet2!$A$1:$A$1000,C$8),1))
 
H

Harlan Grove

Mark Graesser said:
In regard to the COUNTIF. I took a look at this when I first approached
this problem. The OP wants to create an array of counts for each row.
Say there are 2 a's in the first row, 3 in the second, and 4 in the
third. Then the returned array needs to be (2,3,4). When you change
the formula to use the COUNTIF function it looses the row descrimination
and returns an array of (9,9,9). I'm not sure why the first setup works,
but it does. Any insight on that?

By the way, I believe you got the range and criteria backwards in the
first COUNTIF in your complete formula.
Click to expand...
....

No I didn't, but I didn't get it right either.

What I intended was something like A1:A5 containing
{"A";"AB";"BC";"ABC";"X"} and cell B1 containing A, the function call
COUNTIF(B1,"*"&A1:A5&"*") would return the array {1;0;0;0;0}. However, if B1
could be a substring of the entries in A1:A5, this clearly doesn't work.
 
G

Guest

Thanks Harlan
I didn't see that you were testing an array of conditions against the range

Regards
Mark Graesse
(e-mail address removed)


----- Harlan Grove wrote: ----

Mark Graesser said:
In regard to the COUNTIF. I took a look at this when I first approache
this problem. The OP wants to create an array of counts for each row
Say there are 2 a's in the first row, 3 in the second, and 4 in th
third. Then the returned array needs to be (2,3,4). When you chang
the formula to use the COUNTIF function it looses the row descriminatio
and returns an array of (9,9,9). I'm not sure why the first setup works
but it does. Any insight on that
first COUNTIF in your complete formula
Click to expand...
...

No I didn't, but I didn't get it right either

What I intended was something like A1:A5 containin
{"A";"AB";"BC";"ABC";"X"} and cell B1 containing A, the function cal
COUNTIF(B1,"*"&A1:A5&"*") would return the array {1;0;0;0;0}. However, if B
could be a substring of the entries in A1:A5, this clearly doesn't work
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OFFSET cell references 3
Scrabble Value calculation for Welsh words 0
re done Match formula needed 3
Word Create a Phone Book using MailMerge in word 2007 0
match formula needed 2
Transferring only certain rows to seperate worksheet 8
Sumproduct - meet multiple criteria from a list 6
Sumif and Specified Ranges 2

Top