Excel syntax for calculating columns

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.

YRKS
SMadhaviAsked:
Who is Participating?
 
kgerbChief EngineerCommented:
oops, forgot the sum range

=SUMIF(A1:A7,"a",B1:B7)
0
 
kgerbChief EngineerCommented:
use this formula

=sumif(A1:A10, "a")

Kyle
0
 
sshah254Commented:
Or sort by column 2 and then do the subtotals.

SS
0
 
kgerbChief EngineerCommented:
I guess, technically, according to your data the formula should be this

=SUMIF(B1:B7,"a",A1:A7)

Kyle
0
 
Davy2270Commented:
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:
http://www.homeandlearn.co.uk/excel2007/excel2007s7p7.html

Kind regards,
Davy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.