Need some Excel help
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
I am actually pretty handy with Excel, but ran into something today that I've wanted to "automate" via a formula for some time.
Here is what I've got.
I work in IT support and have a report of all of my technicians' tickets. I am trying to report back on how many of their tickets were "late" per technician.
Just for this situation, lets say that all of the tickets are sorted alphabetically by technician, and that there are 3 columns (there are more, but this will be all that matters for the formula). Column A is ticket number Column B is technician's name Column C is either blank or it has a note about a ticket being late.
For getting total ticket counts I've been using the COUNTIF function to count how many times a technician's name shows up on the report. But for this, I want a count of how many times that technician has something entered in Column C.
Only late tickets have an entry in Column C, otherwise it is blank like I said.
I am sure that this will be one of those "oh, duh" moments, but I'm not getting it yet.
Thanks everyone.
Farewell - June 4, 2020
|
|
|
Re: Need some Excel help
|
Joined: Oct 2006
Posts: 484
devotee
|
devotee
Joined: Oct 2006
Posts: 484 |
You're close. CountIf() is going to give you trouble. Use CountA() instead, as in: =COUNTA(C:C)-1 The "minus 1" is to account for your Comment column heading. I never thought that I'd see an Excel question here! What's next, Monarch questions?
|
|
|
Re: Need some Excel help
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
Actually, I got it just a couple of minutes ago with COUNTIFS.
I named the data in Column B as "Tech" and Column C as "RLate"
Formula is: =COUNTIFS(Tech,A5,RLate,"<>")
As a note, A5 is on a second sheet and relates to a technicians' name. I could have plugged in "Tom Smith" or something there.
Anyway, it worked.
Farewell - June 4, 2020
|
|
|
Re: Need some Excel help
|
Joined: Sep 2004
Posts: 5,422
axiomite
|
OP
axiomite
Joined: Sep 2004
Posts: 5,422 |
It wouldn't take much to convert from COUNTIF to COUNTA, but what is the difference for that part?
Last edited by nickbuol; 08/23/10 07:56 PM.
Farewell - June 4, 2020
|
|
|
Re: Need some Excel help
|
Joined: Oct 2006
Posts: 484
devotee
|
devotee
Joined: Oct 2006
Posts: 484 |
If you're getting accurate results with the current formula, I don't see the need to change it, (er, I just noticed that you've edited the post), but maybe you're just wanting a consistent approach or something. (?)
Do you mean the difference between CountIf and CountA?
CountIf allows you to specify comparison criteria, whereas CountA counts cells containing text within the specified range.
Or are you referring to something else?
|
|
|
Re: Need some Excel help
|
Joined: Dec 2003
Posts: 10,420
shareholder in the making
|
shareholder in the making
Joined: Dec 2003
Posts: 10,420 |
This forum never ceases to amaze me with the depth of help we can get here.
Jason M80 v2 VP160 v3 QS8 v2 PB13 Ultra Denon 3808 Samsung 85" Q70
|
|
|
Re: Need some Excel help
|
Joined: Jan 2010
Posts: 578
aficionado
|
aficionado
Joined: Jan 2010
Posts: 578 |
This forum never ceases to amaze me with the depth of help we can get here. On that note, I want to do dynamic ranges... If I add something to a dropdown (thru Validation/List)(and the corresponding row) i'd like it to automatically be added to a range or list (dropdown range for list) Any thoughts on that? To be available for summaries or a VLOOKUP Thanks Jeff
Last edited by wordgasm; 08/24/10 12:06 AM.
Fronts/Wides:M22s in/on Center:VP150/VP100 in/on Hghts,SS,SR:QS8s Sub:EP500 AVR:Onkyo 3008
|
|
|
Re: Need some Excel help
|
Joined: Apr 2007
Posts: 4,877
connoisseur
|
connoisseur
Joined: Apr 2007
Posts: 4,877 |
Jeff,
I'm pretty handy with excel but wouldn't call myself and expert. I am not entirely sure what you are trying to accomplish. If you just want a dropdown list to have new choices appear when you enter new entries into a column associated with the dropdown list then the answer is simple.
For the cell that has the data validation/list in it just modify the range to include rows beyond your existing last row. when new entries are made they will automatically be added to the choice in the dropdown list.
Example: Say you have data in cells A2:A20 and this corresponds to an existing drop down list. In the cell that contains the data validation/dropdown list just make the range something like A2:A100 (or whatever you think the greatest row number will ever be including future entries.) If you enter data into A:21 it will show up in the dropdown list. if you add data into cells A22 it will then be added into the list and so on.
There may be a better way to accomplish this but this seems the easiest. Forgive me if this is not what you are trying to accomplish.
Last edited by terzaghi; 08/24/10 01:18 AM.
-David
|
|
|
Re: Need some Excel help
|
Joined: Oct 2006
Posts: 484
devotee
|
devotee
Joined: Oct 2006
Posts: 484 |
Hi Jeff, there's a bit of a description of dynamic range names on my site (see "4 Sure-Fire Ways to Build Efficient Excel Reports"). Once you have your range name, select the Data Validation, select List from the Allow drop-down, and enter your range name for the source. Be sure to use =lstValues for the range name lstValues (for instance), not just lstValues. Lookups (such as VLOOKUP or HLOOKUP) are great for small workbooks, but will bog down performance for larger workbooks. Use a combination of MATCH and INDEX instead of lookups in those cases. An additional benefit of dynamic ranges used in conjunction with data validation is that the source data for the validated listed need not reside on the same worksheet, which allows for much more flexible and easier to maintain designs. Post back if you have further questions.
Last edited by Kruncher; 08/24/10 02:58 AM. Reason: additional benefits
|
|
|
Re: Need some Excel help
|
Joined: Jan 2010
Posts: 578
aficionado
|
aficionado
Joined: Jan 2010
Posts: 578 |
Hi Jeff, there's a bit of a description of dynamic range names on my site (see "4 Sure-Fire Ways to Build Efficient Excel Reports"). Once you have your range name, select the Data Validation, select List from the Allow drop-down, and enter your range name for the source. Be sure to use =lstValues for the range name lstValues (for instance), not just lstValues. Lookups (such as VLOOKUP or HLOOKUP) are great for small workbooks, but will bog down performance for larger workbooks. Use a combination of MATCH and INDEX instead of lookups in those cases. An additional benefit of dynamic ranges used in conjunction with data validation is that the source data for the validated listed need not reside on the same worksheet, which allows for much more flexible and easier to maintain designs. Post back if you have further questions. Thanks Krunch came up with this per your examples and works as stated. =IF(MATCH(K31,DEALERLIST,0),IF(ISNA(K28),"",INDEX(DEALERRANGE,K28,2))) Next on to dynamic ranges! I've taught myself more than basic excel over the last couple years at work as time allows, and it all works, but I have no idea if I am doing it "right". I've got many workbooks with thousands of VLOOKUPS (they all seem to save in 10-15 seconds. Will probably take about a full day for each workbook to update with ATCH/INDEX and test. But if you say it's worth it, I'll take the plunge. And yes many formulas with double VLOOKUPS in each calulation) Example: =IF($G2="",0,(VLOOKUP($G2,BASERANGE,5,0)*$H2)+IF($I2="",0,(VLOOKUP($I2,BASERANGE,5,0))*$J2)+IF($K2="",0,(VLOOKUP($K2,BASERANGE,5,0))*$L2)) I also use IF/AND/OR alot, any better options for those? Example: =IF($F2="","",IF($F2="Oval",3.1416*(SQRT(2*((POWER($CE2,2)+(POWER($CF2,2))-((POWER(($CE2-$CF2),2)/2.2)))))),0)) =IF($M2="","",IF(OR($M2="Bevel",$M2="Waterfall"),BA2-1,IF($M2="Shaped",BA2-0.625,BA2))) Thanks Again Jeff
Fronts/Wides:M22s in/on Center:VP150/VP100 in/on Hghts,SS,SR:QS8s Sub:EP500 AVR:Onkyo 3008
|
|
|
Forums16
Topics24,943
Posts442,465
Members15,617
|
Most Online2,082 Jan 22nd, 2020
|
|
0 members (),
667
guests, and
4
robots. |
Key:
Admin,
Global Mod,
Mod
|
|
|
|