Creating a nested COUNTIF????

[Simon Lloyd]

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

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

Hope that is clear and hope you can help!

Simo

 

[Max]

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

 

[Simon Lloyd]

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)

 

[Max]

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

 

[Simon Lloyd]

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

Thanks,

Simon

 

[Max]

No prob ! <g>

 

[Max]

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 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.

 

[Simon Lloyd]

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.

 

[Max]

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 !