Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Simon Lloyd
 
Posts: n/a
Default Creating a nested COUNTIF????


Hi all, im trying to get my spreadsheet to count in one cell if certain
criteria appear in two other cells, say for instance
=COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
named range and the number would be from -11 to 11.

So in short i would like the value for "Hours" displayed in Bloggs J
cell (which could be lets say E3) only if Bloggs J appears in the named
range "Name"

Hope that is clear and hope you can help!

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=401673

  #2   Report Post  
Max
 
Posts: n/a
Default

Maybe you mean in E3:

=SUMPRODUCT((Name="Bloggs J")*(Hours=10))

which would return the count you're after in your line
=COUNTIF(Name,"Bloggs J" and Hours,= number)

assuming the "Hours" number = 10
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Simon Lloyd"
wrote in message
...

Hi all, im trying to get my spreadsheet to count in one cell if certain
criteria appear in two other cells, say for instance
=COUNTIF(Name,"Bloggs J" and Hours,= number) where hours is the next
named range and the number would be from -11 to 11.

So in short i would like the value for "Hours" displayed in Bloggs J
cell (which could be lets say E3) only if Bloggs J appears in the named
range "Name"

Hope that is clear and hope you can help!

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:

http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=401673



  #3   Report Post  
Simon Lloyd
 
Posts: n/a
Default


At the moment i have the follwing in the cells, which is great and adds
1 for every instance it sees of the name in the Range "Name" but
instead of counting in column B each instance of the name and adding 1
i need it to see the name Bloggs J in the "name" range and then look
for the value on the same row in the range "Hours" this will then be
totalled with the results from another sheet as you can see in column
G.

Any thoughts?

Simon

COLUMN E

Bloggs J

COLUMN F
=COUNTIF(Name,"Bloggs J")

COLUMN G
=SUM(F40,'Holiday Count'!K33)


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=401673

  #4   Report Post  
Max
 
Posts: n/a
Default

If you'd like to, email over a copy of your file at:
demechanik < at yahoo <dot com

Think a look at your set-up would be clearer

Drop me a note here if you're sending or not
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #5   Report Post  
Simon Lloyd
 
Posts: n/a
Default


will mail you but it won't be until 6pm GMT

Thanks,

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=401673



  #6   Report Post  
Max
 
Posts: n/a
Default

No prob ! <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


  #7   Report Post  
Max
 
Posts: n/a
Default

Here's my best shot at it, Simon,
after studying your detailed set-up and comments:

In sheet: Xtra's & count
------------------------
You have in F2:
=COUNTIF(Name,"Atkinson M")

Instead of hardcoding the names into the formula cell by cell,
we could automate the revised version which includes summing
the figures from the "Hours Given/Taken" col D
by changing it in F2 to just:

=SUMIF($A:$A,E2,$D:$D)

(E2 contains the name: Atkinson M,
E3 contains another name and so on, down col E.
The structure is repeated in cols H, K & N)

And you have in G2:
=SUM(F2,'Holiday Count'!B5)

but this formula in col G is not consistent down col G, as a
random check down G3:G40 reveals the formula jumps about, e.g.:

G3: =SUM(F3,'Holiday Count'!B4)
G4: =SUM(F4,'Holiday Count'!B11)
G7: =SUM(F7,'Holiday Count'!B43)
G38: =SUM(F38,'Holiday Count'!H38)
G40: =SUM(F40,'Holiday Count'!K33)

The haphazard jumps were apparently to point to cells adjacent to the
corresponding specific names located in "'Holiday Count"'s col E and had
been painstakingly put together, cell by cell

Here, think we could try to automate it by changing it in G2 to:

=SUM(F2,IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$A:$B,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),IF(ISNA(VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),0,VLOOKUP(E2,'Holiday
Count'!$J:$K,2,0)),VLOOKUP(E2,'Holiday
Count'!$G:$H,2,0)),VLOOKUP(E2,'Holiday
Count'!$D:$E,2,0)),VLOOKUP(E2,'Holiday Count'!$A:$B,2,0)))

The 4 level nested IF(ISNA(VLOOKUP1(...),IF(ISNA(VLOOKUP2(...), ... )
will automate the looking up of the names in col E against the 4 lists of
names that you have set-up in sheet: Holiday Count, in cols A, D, G & J
and return the number in the adjacent cols B, E, H & K where the name
matches. This of course, presumes that all the names listed in sheet:
Holiday Count are unique names (a scan shows this to be so). If there is no
match found for the names, a zero will be returned

Then just select F2:G2, fill down as far as required

And to propagate the above likewise to
the other similarly structured "pairs" of cols I & J, L & M, O & P

Copy F2:G2, then do a paste special Check "Formulas" OK on I2
This will copy the formulas in F2:G2 relatively into I2:J2,
w/o impacting the formats, etc

Then just select & fill I2:J2 down as far as the extent done for cols F & G

And repeat the process for cols L & M, and cols O & P

I will email to you the revised file later tonight
(can't access to yahoo right now)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Simon's cover note:

As promised here is the workbook but a little late, you will see on the
sheet Xtra's and Count that there are some cells that have names near
them, column F looks for an instance of the name in column E in the named
range and column called "Name", then column G looks at the total for the
corresponding name on the Holiday Count sheet and adds the value from
column F.

This all works fine when counting single occurences but now i have
added a column called "Hours Given/Taken" and the range is called
"Hourlist", I want to be able to count up the hours from column D and put
them in column F for the corresponding names i then want the value of this
cell adding/subtracting from the corresponding name total on the Hloiday
Count sheet and being displayed in column G on the Xtra's & count sheet.

This sheet has list validation for coulumns A:D so its point and click (the
people that use it dont like using computers so i designed it to be as
simple as possible, on the Holidays sheet you just enter the persons
index number press enter and the name appears from a look up sheet. NB i
need to keep the N/A# as it helps me see if anyone has delete the formulas
as protecting the sheets for the way it is used is a pain!), thee are
other features ive built in but they shouldnt interfere.

When the "log in" box pops up just enter <snipped and it will allow you
to proceed.


  #8   Report Post  
Simon Lloyd
 
Posts: n/a
Default


Max,

Thanks, i've been working nights so took me a little time to work out
what you had proposed and the automation works fine ( a lot smarter
too!)......there was one thing i needed the workbook to do, that is
when hours are added or deducted on the Xtra's sheet they need to be
added or removed from the total on the Holiday Count sheet, i realise
that this sort of creates a circular calculation which Excel doesn't
like, so maybe i have steered you wrong in the first place as to the
totalling system.

The people that use this will at first glance at the holiday count to
see if a person has any entitlement left then will use the Holidays
sheet to book more hours, however the Xtra's sheet will be used if
hours are given or taken for any other reason other than legitimate
holidays so the total needs to be shown here too (perhaps its a strange
concept to have the 2 totals but it needed to be shown for simplicity
for the users).

Hope this clears it up a little.............again thanks for all your
help!

Simon.


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=401673

  #9   Report Post  
Max
 
Posts: n/a
Default

You're welcome, Simon !
I'm out of further ideas here to offer you.

Monitor your thread here awhile
for possible insights from other folks
Or you may wish to put in a new post ..

All the best !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested Countif wayne75 Excel Worksheet Functions 2 June 3rd 05 04:30 PM
COUNTIF and Nested Functions? Larry Novida Excel Worksheet Functions 3 May 29th 05 07:05 PM
Countif Function -Nested Angi Excel Discussion (Misc queries) 7 May 4th 05 07:04 PM
Creating an EXCEL COUNTIF formula for a range of values Pat Walsh Excel Discussion (Misc queries) 5 January 21st 05 03:57 PM
Countif - Countif maswinney Excel Worksheet Functions 3 November 16th 04 12:06 AM


All times are GMT +1. The time now is 11:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"