Adding Rows in Excel

Posted on 2012-09-17
Last Modified: 2012-09-18

On my attached sheet, 'Totals' is to be a count of all the different vehicle types on the 'Type' sheet.  When I am looking to have done is to have the count of the 'Type of Vehicle' in the 'Type' sheet placed into the appropriate cell on the 'Totals' sheet.  For my example, on the 'Total's sheet, it should say: Cars - 3, Trucks 4.  (this is a 1% sample of my data - my actual data is 90,000+ rows, with many different types of vehicles).  

I know I can perform a sort/filter to get my totals, but wanted to see if I could get a macro or something to do it for me instead since the numbers are always changing.  In addition, once you get me started with a macro, then I can add the other vehicle types.

Please advise with any questions.

Thank you,
Question by:James0903
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    If you make A3=CAR and A4=TRUCK on the totals sheet then enter this formula in B3 and copy down

    =SUMIF(Type!B:B,SUBSTITUTE(A3,"TOTAL OF ",""),Type!C:C)

    Author Comment

    Thank you.  I have performed this but not sure why it is not working.  Did I enter incorrectly?  (see attached).
    LVL 5

    Accepted Solution

    You need a COUNTIF, not s SUMIF.

    Put the following in cell B3 on the Totals worksheet and copy it down to B4 as well.


    Author Closing Comment

    That worked.  Thank you!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now