?
Solved

Excel syntax for calculating columns

Posted on 2011-10-19
5
Medium Priority
?
270 Views
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.

YRKS
0
Comment
Question by:SMadhavi
  • 3
5 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 36993486
use this formula

=sumif(A1:A10, "a")

Kyle
0
 
LVL 12

Accepted Solution

by:
kgerb earned 2000 total points
ID: 36993496
oops, forgot the sum range

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

Expert Comment

by:sshah254
ID: 36993499
Or sort by column 2 and then do the subtotals.

SS
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36993519
I guess, technically, according to your data the formula should be this

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

Kyle
0
 
LVL 3

Expert Comment

by:Davy2270
ID: 36998554
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

616 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