Hopefully someone can help me with this.

I have the following source data, 2 rows and 2 columns.:

A B
1 2 ?
2 1 ?

In column A I would enter a number.
Column B would be a forumla that returns a result, given the data
below.

Then I have the data I want to lookup in, 2 rows and 4 columns (in this
example)

E F G H
1 1 X X
2 2 X

Column E would be a unique number, generated via =ROW()
Column F,G,H would have "X"'s in the column (ie if it was checked off)

Now given that above.
If someone entered a "2" in Column A, Row 1 above, I want a formula
that will search through E1..E2, and if it matches what was entered
(2), then it go through that same row (2) (in this case F2..H2) and
counts all the X's - in this case it would return 1.
If someone entered a "1" A2, it would return "2", since it found "1" in
E1, and thus searched F1..H1 for "X"'s and found 2 of them.

Hopefully that above shows up ok with ugly ascii art to illustrate the
data.
Also - the above is a contrived example, but it relates to a real-world
example im trying to solve.

Any help would be greatly appreciated. I suspect its something along
the lines of using SUMIF, COUNTIF, V/HLOOKUP, etc. Ive come close, but
im kinda stuck on a few parts (namely once i've matches the number the
person entered in column E, i need to do a COUNTIF( on that same ROW -
but i dont know how to find out that row).

Thanks!

Opps, two mistakes

"If someone entered a "1" A2"
should read
"If someone entered a "1" IN A2"

AND

"and thus searched F1..H1 for "X"'s and found 2 of them"
should read
"and thus searched F1..H1 for "X"'s and found ONE of them"

Sorry

Hi

try
=COUNTIF(OFFSET($E$1,MATCH(A1,$E$1:$E$2)-1,1,1,3),"x")

Amend ranges to suit

--
Regards

Roger Govier


wrote in message
ps.com...
Hopefully someone can help me with this.

I have the following source data, 2 rows and 2 columns.:

A B
1 2 ?
2 1 ?

In column A I would enter a number.
Column B would be a forumla that returns a result, given the data
below.

Then I have the data I want to lookup in, 2 rows and 4 columns (in
this
example)

E F G H
1 1 X X
2 2 X

Column E would be a unique number, generated via =ROW()
Column F,G,H would have "X"'s in the column (ie if it was checked off)

Now given that above.
If someone entered a "2" in Column A, Row 1 above, I want a formula
that will search through E1..E2, and if it matches what was entered
(2), then it go through that same row (2) (in this case F2..H2) and
counts all the X's - in this case it would return 1.
If someone entered a "1" A2, it would return "2", since it found "1"
in
E1, and thus searched F1..H1 for "X"'s and found 2 of them.

Hopefully that above shows up ok with ugly ascii art to illustrate the
data.
Also - the above is a contrived example, but it relates to a
real-world
example im trying to solve.

Any help would be greatly appreciated. I suspect its something along
the lines of using SUMIF, COUNTIF, V/HLOOKUP, etc. Ive come close, but
im kinda stuck on a few parts (namely once i've matches the number the
person entered in column E, i need to do a COUNTIF( on that same ROW -
but i dont know how to find out that row).

Thanks!

Much thanks Roger - I will try that tommorow at work.

Roger Govier wrote:
Hi

try
=COUNTIF(OFFSET($E$1,MATCH(A1,$E$1:$E$2)-1,1,1,3),"x")

Amend ranges to suit

--
Regards

Roger Govier


wrote in message
ps.com...
Hopefully someone can help me with this.

I have the following source data, 2 rows and 2 columns.:

A B
1 2 ?
2 1 ?

In column A I would enter a number.
Column B would be a forumla that returns a result, given the data
below.

Then I have the data I want to lookup in, 2 rows and 4 columns (in
this
example)

E F G H
1 1 X X
2 2 X

Column E would be a unique number, generated via =ROW()
Column F,G,H would have "X"'s in the column (ie if it was checked off)

Now given that above.
If someone entered a "2" in Column A, Row 1 above, I want a formula
that will search through E1..E2, and if it matches what was entered
(2), then it go through that same row (2) (in this case F2..H2) and
counts all the X's - in this case it would return 1.
If someone entered a "1" A2, it would return "2", since it found "1"
in
E1, and thus searched F1..H1 for "X"'s and found 2 of them.

Hopefully that above shows up ok with ugly ascii art to illustrate the
data.
Also - the above is a contrived example, but it relates to a
real-world
example im trying to solve.

Any help would be greatly appreciated. I suspect its something along
the lines of using SUMIF, COUNTIF, V/HLOOKUP, etc. Ive come close, but
im kinda stuck on a few parts (namely once i've matches the number the
person entered in column E, i need to do a COUNTIF( on that same ROW -
but i dont know how to find out that row).

Thanks!

That worked Roger - much thanks! Very Appreciated!

Actually, this example _dosent_ seem to work if the data isnt in order
(which it isnt)
For example, if the lookup data is formatted like so:

E F G H
1 2 X X
2 1 X
3 3 X

The given formula gives an error.

Any suggestions?

Hi

The try this modification
=COUNTIF(OFFSET($E$1,MATCH(A1,$E$1:$E$3,0)-1,1,1,3),"x")

--
Regards

Roger Govier


wrote in message
oups.com...

Actually, this example _dosent_ seem to work if the data isnt in order
(which it isnt)
For example, if the lookup data is formatted like so:

E F G H
1 2 X X
2 1 X
3 3 X

The given formula gives an error.

Any suggestions?