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
[email protected]

[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

"vikkam" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> [email protected]
>
>
> +-------------------------------------------------------------------+
> |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
>

[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

[geoff_francis_cox]

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
> [email protected]
>
>
> +-------------------------------------------------------------------+
> |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
>
>

[Andrea Jones]

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
> [email protected]
>
>
> +-------------------------------------------------------------------+
> |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
>
>

[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.