Solved

# Excel syntax for calculating columns

Posted on 2011-10-19
Medium Priority
270 Views
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
• 3

LVL 12

Expert Comment

ID: 36993486
use this formula

=sumif(A1:A10, "a")

Kyle
0

LVL 12

Accepted Solution

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

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

LVL 9

Expert Comment

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

SS
0

LVL 12

Expert Comment

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

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

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…
###### Suggested Courses
Course of the Month7 days, 19 hours left to enroll