Home |
Search |
Today's Posts |
#1
|
|||
|
|||
counting number of particular items in a list
I am making a monthly file of all vehicles sold during the month on day to day basis. I want to have a count of particular type of vehicles i. e. s number of saabs, and how many 9-2 or 9-3's. Or chevroletss and how many of that trail balzers etc. File attached. Thanks much for the help. vik kamdar +-------------------------------------------------------------------+ |Filename: pr_monthly_report_forum.zip | |Download: http://www.excelforum.com/attachment.php?postid=3559 | +-------------------------------------------------------------------+ -- vikkam ------------------------------------------------------------------------ vikkam's Profile: http://www.excelforum.com/member.php...fo&userid=1266 View this thread: http://www.excelforum.com/showthread...hreadid=384179 |
#2
|
|||
|
|||
No file attached.
Sounds like you could use a pivot table to summarize the data you have collected during the month and make a nice report. -- Carlos "vikkam" wrote in message ... I am making a monthly file of all vehicles sold during the month on day to day basis. I want to have a count of particular type of vehicles i. e. s number of saabs, and how many 9-2 or 9-3's. Or chevroletss and how many of that trail balzers etc. File attached. Thanks much for the help. vik kamdar +-------------------------------------------------------------------+ |Filename: pr_monthly_report_forum.zip | |Download: http://www.excelforum.com/attachment.php?postid=3559 | +-------------------------------------------------------------------+ -- vikkam ------------------------------------------------------------------------ vikkam's Profile: http://www.excelforum.com/member.php...fo&userid=1266 View this thread: http://www.excelforum.com/showthread...hreadid=384179 |
#3
|
|||
|
|||
Hi Vikkam
Have you tried using the COUNTIF function? Basically choose a cell to display the result in and then use an argument similar to the following COUNTIF(A2:A9,"Saab") Obviously the A2 and A9 values is the cell range that you want to perform the count in, and the Saab is the item that you want to perform the count on. Use he Help function in Excel for extra detail if you need it. Geoff "vikkam" wrote: I am making a monthly file of all vehicles sold during the month on day to day basis. I want to have a count of particular type of vehicles i. e. s number of saabs, and how many 9-2 or 9-3's. Or chevroletss and how many of that trail balzers etc. File attached. Thanks much for the help. vik kamdar +-------------------------------------------------------------------+ |Filename: pr_monthly_report_forum.zip | |Download: http://www.excelforum.com/attachment.php?postid=3559 | +-------------------------------------------------------------------+ -- vikkam ------------------------------------------------------------------------ vikkam's Profile: http://www.excelforum.com/member.php...fo&userid=1266 View this thread: http://www.excelforum.com/showthread...hreadid=384179 |
#4
|
|||
|
|||
Depending on whether you are wanting to count dependent on the values in one
column or two you can use the COUNTIF function (see http://www.allaboutoffice.co.uk/excondagg.htm ) or an array formula (see http://www.allaboutoffice.co.uk/exarray.htm ). Andrea Jones www.allaboutoffice.co.uk www.stratatraining.co.uk www.allaboutclait.com "vikkam" wrote: I am making a monthly file of all vehicles sold during the month on day to day basis. I want to have a count of particular type of vehicles i. e. s number of saabs, and how many 9-2 or 9-3's. Or chevroletss and how many of that trail balzers etc. File attached. Thanks much for the help. vik kamdar +-------------------------------------------------------------------+ |Filename: pr_monthly_report_forum.zip | |Download: http://www.excelforum.com/attachment.php?postid=3559 | +-------------------------------------------------------------------+ -- vikkam ------------------------------------------------------------------------ vikkam's Profile: http://www.excelforum.com/member.php...fo&userid=1266 View this thread: http://www.excelforum.com/showthread...hreadid=384179 |
#5
|
|||
|
|||
I have attached the file and can see it in the thead. If you allow I can send it to you either as zip or xls. I am not familiar with pivot tables. Thanks much. vikram -- vikkam ------------------------------------------------------------------------ vikkam's Profile: http://www.excelforum.com/member.php...fo&userid=1266 View this thread: http://www.excelforum.com/showthread...hreadid=384179 |
#6
|
|||
|
|||
Tried that but it gives me count 0 even though there are atleast 5 Saabs. -- vikkam ------------------------------------------------------------------------ vikkam's Profile: http://www.excelforum.com/member.php...fo&userid=1266 View this thread: http://www.excelforum.com/showthread...hreadid=384179 |
#7
|
|||
|
|||
Considereing the file you are using, you could, for the purpose of producing your report, copy the worksheet, block-remove all Merge Cells (by selecting All Data, and rightmouse Format Cells, and untick the Merge) Then select Column I (Year-Model) and select Data, Sort and sort over column I Then select column I and click Data, Subtotals, and select the Count function. On the sub-totalled display Click the number 2 shown as the column header (to the left of the A for column A) and the numbers you require will be displayed. You may need to make column I right-alligned, and column J left alligned for that report. Hope this helps -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=384179 |
#8
|
|||
|
|||
Thanks for your reply. I was looking to have continuous total running at the bottom giving me info abt no of particalr type of vehicles sold. vikram -- vikkam ------------------------------------------------------------------------ vikkam's Profile: http://www.excelforum.com/member.php...fo&userid=1266 View this thread: http://www.excelforum.com/showthread...hreadid=384179 |
#9
|
|||
|
|||
For a running total, you can use multiple 'countif' lines, they do work, but you would need to specify the 'if' exactly as shown in the column, (ie "05 / Chev / Silverado" ) and there are many models that you have used. (or you could extract the 'Silverado' to a helper column and use that) Perhaps list the models, ie "05 / Chev / Silverado", in G130 onwards and in I130 put =COUNTIF(I$7:I$92,G130) and drag that to the end of the listed models. If you use the multiple CountIf method, you might need to check that the sum of the displays were equal to the count of the total listed items via countA(I7:i92 - (non-blank cells), so that you would be forwarned when a new model (or a mispelling) occurred and a new countif were required. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=384179 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I format a cell in excel based on a list of about 20 items? | Excel Discussion (Misc queries) | |||
finding the second largest number in a list | Excel Discussion (Misc queries) | |||
Pivot Tables, can I use an external list to select data items? | Excel Worksheet Functions | |||
Lookup closest number in list | Excel Discussion (Misc queries) | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) |