Offset Formula Question
 

I need to write something, preferably forumla, but It can be VBA if it
has to be, that counts instances of "Y" offset to instances of a given
name.
So, for example:

John Y
Bob N
Jan N
Bob Y
Jerry N
John Y

It would find the instances of the name "John" in column A and then
look to see how many "Y"'s he has in column B.
The formula here would return 2.
For Bob, it would return 1.
For everyone else, 0.

The formula needs to use a cell reference for the search criteria.
I need to point to a cell that has the name "John" in it as opposed to
having the name "John" in the formula.
The names will change often.

I have looked into offsets and vlookups and I am clearly going the
wrong direction.
Any ideas?
Thank-you,
-Joel

Offset Formula Question
 

=SUMPRODUCT((A1:A6=C1)*(B1:B6="Y")) with "John" in C1

Alan Beban

J. Vandenberg wrote:
I need to write something, preferably forumla, but It can be VBA if it
has to be, that counts instances of "Y" offset to instances of a given
name.
So, for example:

John Y
Bob N
Jan N
Bob Y
Jerry N
John Y

It would find the instances of the name "John" in column A and then
look to see how many "Y"'s he has in column B.
The formula here would return 2.
For Bob, it would return 1.
For everyone else, 0.

The formula needs to use a cell reference for the search criteria.
I need to point to a cell that has the name "John" in it as opposed to
having the name "John" in the formula.
The names will change often.

I have looked into offsets and vlookups and I am clearly going the
wrong direction.
Any ideas?
Thank-you,
-Joel

Offset Formula Question
 

One way:

Name in D1:


=SUMPRODUCT(--(A1:A100=D1),--(B1:B100="Y"))

In article ,
(J. Vandenberg) wrote:

I need to write something, preferably forumla, but It can be VBA if it
has to be, that counts instances of "Y" offset to instances of a given
name.
So, for example:

John Y
Bob N
Jan N
Bob Y
Jerry N
John Y

It would find the instances of the name "John" in column A and then
look to see how many "Y"'s he has in column B.
The formula here would return 2.
For Bob, it would return 1.
For everyone else, 0.

The formula needs to use a cell reference for the search criteria.
I need to point to a cell that has the name "John" in it as opposed to
having the name "John" in the formula.
The names will change often.

I have looked into offsets and vlookups and I am clearly going the
wrong direction.
Any ideas?
Thank-you,
-Joel

Offset Formula Question
 

Use this

=SUMPRODUCT((A1:A11="John")*(B1:B11="Y"))

with a cell named name where you type John in for example
=SUMPRODUCT((A1:A11=name)*(B1:B11="Y"))


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"J. Vandenberg" wrote in message om...
I need to write something, preferably forumla, but It can be VBA if it
has to be, that counts instances of "Y" offset to instances of a given
name.
So, for example:

John Y
Bob N
Jan N
Bob Y
Jerry N
John Y

It would find the instances of the name "John" in column A and then
look to see how many "Y"'s he has in column B.
The formula here would return 2.
For Bob, it would return 1.
For everyone else, 0.

The formula needs to use a cell reference for the search criteria.
I need to point to a cell that has the name "John" in it as opposed to
having the name "John" in the formula.
The names will change often.

I have looked into offsets and vlookups and I am clearly going the
wrong direction.
Any ideas?
Thank-you,
-Joel

Offset Formula Question
 

On 28 Oct 2003 08:33:03 -0800, (J. Vandenberg) wrote:

I need to write something, preferably forumla, but It can be VBA if it
has to be, that counts instances of "Y" offset to instances of a given
name.
So, for example:

John Y
Bob N
Jan N
Bob Y
Jerry N
John Y

It would find the instances of the name "John" in column A and then
look to see how many "Y"'s he has in column B.
The formula here would return 2.
For Bob, it would return 1.
For everyone else, 0.


You might also want to look at Pivot Table reports.


--ron

Offset Formula Question
 

Ack, no experience with those...
I will try that out though, thanks.
Anyone else have any other ideas?!?
-Joel



Ron Rosenfeld wrote in message . ..
On 28 Oct 2003 08:33:03 -0800, (J. Vandenberg) wrote:

I need to write something, preferably forumla, but It can be VBA if it
has to be, that counts instances of "Y" offset to instances of a given
name.
So, for example:

John Y
Bob N
Jan N
Bob Y
Jerry N
John Y

It would find the instances of the name "John" in column A and then
look to see how many "Y"'s he has in column B.
The formula here would return 2.
For Bob, it would return 1.
For everyone else, 0.


You might also want to look at Pivot Table reports.


--ron

Offset Formula Question
 

Take an hour or two and familiarize yourself with pivottables. You'll see that
you can get a lot done really quickly. Once you get a summary you like, save
it. Then you can continue playing. If you screw it up (really easy to do when
you're learning), just go back to that saved version.

Here are some links for pivottable info:

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/...ivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

"J. Vandenberg" wrote:

Ack, no experience with those...
I will try that out though, thanks.
Anyone else have any other ideas?!?
-Joel

Ron Rosenfeld wrote in message . ..
On 28 Oct 2003 08:33:03 -0800, (J. Vandenberg) wrote:

I need to write something, preferably forumla, but It can be VBA if it
has to be, that counts instances of "Y" offset to instances of a given
name.
So, for example:

John Y
Bob N
Jan N
Bob Y
Jerry N
John Y

It would find the instances of the name "John" in column A and then
look to see how many "Y"'s he has in column B.
The formula here would return 2.
For Bob, it would return 1.
For everyone else, 0.


You might also want to look at Pivot Table reports.


--ron

--

Dave Peterson