counting number of particular items in a list

[vikkam]

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
(e-mail address removed)


+-------------------------------------------------------------------+
|Filename: pr_monthly_report_forum.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3559 |
+-------------------------------------------------------------------+

 

[Carlos Antenna]

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

 

[Guest]

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

 

[Guest]

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]

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]

Tried that but it gives me count 0 even though there are atleast 5
Saabs.

 

[Bryan Hessey]

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

 

[vikkam]

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

 

[Bryan Hessey]

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.