Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
No prob ! <g
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested Countif | Excel Worksheet Functions | |||
COUNTIF and Nested Functions? | Excel Worksheet Functions | |||
Countif Function -Nested | Excel Discussion (Misc queries) | |||
Creating an EXCEL COUNTIF formula for a range of values | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions |