Offset Formula Question

  • Thread starter Thread starter J. Vandenberg
  • Start date Start date
J

J. Vandenberg

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
 
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"))
 
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.
Click to expand...


You might also want to look at Pivot Table reports.


--ron
 
Ack, no experience with those...
I will try that out though, thanks.
Anyone else have any other ideas?!?
-Joel
 
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/Excel/Pivots/pivottables.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/assistance/2002/articles/xlconPT101.aspx
 
Back
Top