SMadhavi
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or sort by column 2 and then do the subtotals.
SS
SS
I guess, technically, according to your data the formula should be this
=SUMIF(B1:B7,"a",A1:A7)
Kyle
=SUMIF(B1:B7,"a",A1:A7)
Kyle
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
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
=sumif(A1:A10, "a")
Kyle