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

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

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

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

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

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

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

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

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