Ok firstly in your responses (assuming there are any!) please consider
me a retard at least that way I'll be more familiar with the terminlogy
and formulae used. This is going to be a pain to explain as I cant use
tables on this forum it seems so forgive the formatting :P

Also bear in mind im operating off a very locked system and I have no
net access there and a very cut down version of excel so I dont have
access to plugins or some of the more advanced functions only available
on custom installs.

In excel how can I count a specific set of values and have it return
them:

so there would be a user entry box, enter a name and it looks up all
the data associated with that name (which would be amount of accounts
opened in this case and accounts w/c opened both of which are simple
numbers which would add up across a few worksheets representing days of
the week.)

The spreadsheet setup is currently this

Worksheets:

Sunday, monday, etc etc, saturday

on each worksheet is this topline below which a few 100 odd spaces
ready for the data that I input.

Accounts

WW LIN CW HOM F&B ACC MEN COS CS NAME

Accs w/c

WW LIN CW HOM F&B ACC MEN COS CS

All of this is in the same line on the sheet and the only useful
information under each is a simple "1" this is the only value that
appears in these columns apart from obviously the name of the person.
There is always a "1" in one of the columns if a name is also entered,
these are entered by me from the data im working off, it nice and
simply means NAME has opened 1 account the next box along is acc w/c
and doesnt always have a value in, but if it does then that value will
always match the same header in "accounts" and will also be a "1"

So what I'd want is nice and simply say I want to look up name "blah"
then it will list

account acc w/c
blah 2 1

for example and it would list that if it found 2 accounts opened as
well as 1 acc w/c for that name.

More ideally I'd like a permanant output based of all the worksheets
which lists EVERYONE found and exactly how many accounts and acc w/c
are attributed to them.

sorry thats all a bit of longwinded way of explaining, this is all
probably laughably easy to do but I would be very grateful if someone
could help as this would save me soooooo much time.

thanks!


--
dave99
------------------------------------------------------------------------
dave99's Profile: http://www.excelforum.com/member.php...o&userid=30221
View this thread: http://www.excelforum.com/showthread...hreadid=498989

Can you give some more details. Like how many account columns do you
have, from which column to which column, how many rows, are there
specific columns you want to only look into if so which ones? Which row
is your Header row? Which columns do you have your names into? Things of
that nature. In the meantime have a look at the formula below, you might
be able to adapt it to your specifications.

=SUMPRODUCT((Sheet2!$A$2:$A$20=$A2)*(Sheet2!$B$1:$ D$1<""),Sheet2!$B$2:$D$20)
whe
Sheet2!$A$2:$A$20 in the range with the list of names in sheet 2
A2 is the cell with the name you want to lookup in your lookup sheet
Sheet2!$B$1:$D$1 is your header row with account names
Sheet2!$B$2:$B$20 is the range with your values

if you want to add the values from a specific column then you would use
something like

=SUMPRODUCT((Sheet2!$A$2:$A$20=A2)*(Sheet2!$B$1:$D $1="acc1"),Sheet2!$B$2:$D$20)
acc1 would be an account name in B1, C1 or D1 of Sheet 2

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=498989