Previous Thread
Next Thread
Print Thread
Rate Thread
Page 1 of 2 1 2
Need some Excel help
#319652 08/23/10 07:30 PM
Joined: Sep 2004
Posts: 5,422
axiomite
OP Offline
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
nickbuol #319656 08/23/10 07:40 PM
Joined: Oct 2006
Posts: 484
devotee
Offline
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! smile

What's next, Monarch questions? wink

Re: Need some Excel help
Kruncher #319657 08/23/10 07:44 PM
Joined: Sep 2004
Posts: 5,422
axiomite
OP Offline
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
nickbuol #319658 08/23/10 07:49 PM
Joined: Sep 2004
Posts: 5,422
axiomite
OP Offline
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
nickbuol #319659 08/23/10 08:00 PM
Joined: Oct 2006
Posts: 484
devotee
Offline
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
Kruncher #319675 08/23/10 09:26 PM
Joined: Dec 2003
Posts: 10,420
J
shareholder in the making
Offline
shareholder in the making
J
Joined: Dec 2003
Posts: 10,420
This forum never ceases to amaze me with the depth of help we can get here. smile


Jason
M80 v2
VP160 v3
QS8 v2
PB13 Ultra
Denon 3808
Samsung 85" Q70
Re: Need some Excel help
jakewash #319718 08/24/10 12:04 AM
Joined: Jan 2010
Posts: 578
G
aficionado
Offline
aficionado
G
Joined: Jan 2010
Posts: 578
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

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
Glitchy #319723 08/24/10 01:15 AM
Joined: Apr 2007
Posts: 4,877
connoisseur
Offline
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
Glitchy #319738 08/24/10 02:47 AM
Joined: Oct 2006
Posts: 484
devotee
Offline
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
Kruncher #319780 08/24/10 02:28 PM
Joined: Jan 2010
Posts: 578
G
aficionado
Offline
aficionado
G
Joined: Jan 2010
Posts: 578
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
Page 1 of 2 1 2

Moderated by  alan, Amie, Andrew, axiomadmin, Brent, Debbie, Ian, Jc 

Link Copied to Clipboard

Need Help Graphic

Forum Statistics
Forums16
Topics24,940
Posts442,457
Members15,616
Most Online2,082
Jan 22nd, 2020
Top Posters
Ken.C 18,044
pmbuko 16,441
SirQuack 13,840
CV 12,077
MarkSJohnson 11,458
Who's Online Now
0 members (), 402 guests, and 3 robots.
Key: Admin, Global Mod, Mod
Newsletter Signup
Powered by UBB.threads™ PHP Forum Software 7.7.4