You are not logged in. [Log In]


Forums » General Discussion » The Water Cooler » Need some Excel help

Page 1 of 2 1 2 >
Topic Options
Rate This Topic
#319652 - 08/23/10 03:30 PM Need some Excel help
nickbuol Offline
axiomite

Registered: 09/16/04
Posts: 5319
Loc: Marion, IA
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.
_________________________
http://ht.buol.us
2-M60s, VP180, 8-M3s, SVS 20-39PCi, DIY Sub, 8-Shakers, JVC RS45, Anthem MRX-1120

Top
#319656 - 08/23/10 03:40 PM Re: Need some Excel help [Re: nickbuol]
Kruncher Offline
devotee

Registered: 10/05/06
Posts: 484
Loc: Maple Ridge, BC
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! smile

What's next, Monarch questions? wink

Top
#319657 - 08/23/10 03:44 PM Re: Need some Excel help [Re: Kruncher]
nickbuol Offline
axiomite

Registered: 09/16/04
Posts: 5319
Loc: Marion, IA
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.
_________________________
http://ht.buol.us
2-M60s, VP180, 8-M3s, SVS 20-39PCi, DIY Sub, 8-Shakers, JVC RS45, Anthem MRX-1120

Top
#319658 - 08/23/10 03:49 PM Re: Need some Excel help [Re: nickbuol]
nickbuol Offline
axiomite

Registered: 09/16/04
Posts: 5319
Loc: Marion, IA

It wouldn't take much to convert from COUNTIF to COUNTA, but what is the difference for that part?


Edited by nickbuol (08/23/10 03:56 PM)
_________________________
http://ht.buol.us
2-M60s, VP180, 8-M3s, SVS 20-39PCi, DIY Sub, 8-Shakers, JVC RS45, Anthem MRX-1120

Top
#319659 - 08/23/10 04:00 PM Re: Need some Excel help [Re: nickbuol]
Kruncher Offline
devotee

Registered: 10/05/06
Posts: 484
Loc: Maple Ridge, BC
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?

Top
#319675 - 08/23/10 05:26 PM Re: Need some Excel help [Re: Kruncher]
jakewash Offline
shareholder in the making

Registered: 12/26/03
Posts: 10415
Loc: Calgary, Alberta
This forum never ceases to amaze me with the depth of help we can get here. smile
_________________________
Jason
-----------------
TTTHHHPPPPPTTTT!

My HT

Top
#319718 - 08/23/10 08:04 PM Re: Need some Excel help [Re: jakewash]
Glitchy Offline
aficionado

Registered: 01/28/10
Posts: 578
Loc: Blueridge Foothills, NC
Originally Posted By: jakewash
This forum never ceases to amaze me with the depth of help we can get here. smile


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


Edited by wordgasm (08/23/10 08:06 PM)
_________________________
Fronts/Wides:M22s in/on
Center:VP150/VP100 in/on
Hghts,SS,SR:QS8s
Sub:EP500
AVR:Onkyo 3008

Top
#319723 - 08/23/10 09:15 PM Re: Need some Excel help [Re: Glitchy]
terzaghi Offline
connoisseur

Registered: 04/04/07
Posts: 4870
Loc: Tulsa, Oklahoma
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.


Edited by terzaghi (08/23/10 09:18 PM)
_________________________
-David

Top
#319738 - 08/23/10 10:47 PM Re: Need some Excel help [Re: Glitchy]
Kruncher Offline
devotee

Registered: 10/05/06
Posts: 484
Loc: Maple Ridge, BC
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.


Edited by Kruncher (08/23/10 10:58 PM)
Edit Reason: additional benefits

Top
#319780 - 08/24/10 10:28 AM Re: Need some Excel help [Re: Kruncher]
Glitchy Offline
aficionado

Registered: 01/28/10
Posts: 578
Loc: Blueridge Foothills, NC
Originally Posted By: Kruncher
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

Top
Page 1 of 2 1 2 >

Moderator:  alan, Amie, Andrew, axiomadmin, Brent, Debbie, Ian, Jc 
Forum Stats

15,224 Registered Members
15 Forums
23,991 Topics
424,046 Posts

Most users ever online:
883 @ 03/04/17 05:06 PM

Top Posters
Ken.C 18044
pmbuko 16437
SirQuack 13567
CV 11677
MarkSJohnson 11437
0 registered ()
67 Guests and
3 Spiders online.
Key: Admin, Global Mod, Mod
Newsletter Signup