Excel syntax for calculating columns

Posted on 2011-10-19
Last Modified: 2012-05-12
Hi there,

I have huge data in the format
col1  col2     col3   col4
1        a
0    u
1     a
2      u
3      a
0      a
0    a
0     a

I need to do calculations such that I get a sum of all the numbers in colum a where col2 is a

ie. sum(col1 if col2=a)

I think will need to write it as
If (B1='a', sum(a1))
need help with syntax in excel.

Question by:SMadhavi
    LVL 12

    Expert Comment

    use this formula

    =sumif(A1:A10, "a")

    LVL 12

    Accepted Solution

    oops, forgot the sum range

    LVL 9

    Expert Comment

    Or sort by column 2 and then do the subtotals.

    LVL 12

    Expert Comment

    I guess, technically, according to your data the formula should be this


    LVL 3

    Expert Comment

    You might want to consider using a pivot table.
    A pivot table is a reporting feature in excel which can group values and make sumations from a dataset.
    Learn how to use it and it will bring you a great productivity advantage.
    Also when applying formulas to the total length of large data sets will degrade performance of your workbook.

    Here's a good tutorial on pivot tables:

    Kind regards,

    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.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Array Formula to add additional step 1 22
    VBA exception error 5 22
    Updating Pivot Table within VBA 5 28
    Excel Averageifs 2 15
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    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…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    728 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

    15 Experts available now in Live!

    Get 1:1 Help Now