Sum of cells if another cell contains a specific value

  • Thread starter Thread starter BigFish311
  • Start date Start date
B

BigFish311

Column A Column B
em 5
mm 6
tnk 7
em 545
mm 45
tnk 78
tnk 746
tnk 457
mm 184
mm 154
em 157
mm 1557

Above is a short example of an excel file I have andI was wondering if
there is a way without re-sorting the data and with out selecting each
inidividual cell to add up all the cells in column B if Column A
equals "em". I am only asking this because I work with a couple
managers that need the data to be in a specific format and wanted me
to keep it update with automatic calculations at the bottom to sum all
the data for each of the functions in column A.
 
=SUMIF(A1:A11,"em",B1:B11)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Column A Column B
| em 5
| mm 6
| tnk 7
| em 545
| mm 45
| tnk 78
| tnk 746
| tnk 457
| mm 184
| mm 154
| em 157
| mm 1557
|
| Above is a short example of an excel file I have andI was wondering if
| there is a way without re-sorting the data and with out selecting each
| inidividual cell to add up all the cells in column B if Column A
| equals "em". I am only asking this because I work with a couple
| managers that need the data to be in a specific format and wanted me
| to keep it update with automatic calculations at the bottom to sum all
| the data for each of the functions in column A.
|
 
=SUMIF(A1:A11,"em",B1:B11)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|
| Column A Column B
| em 5
| mm 6
| tnk 7
| em 545
| mm 45
| tnk 78
| tnk 746
| tnk 457
| mm 184
| mm 154
| em 157
| mm 1557
|
| Above is a short example of an excel file I have andI was wondering if
| there is a way without re-sorting the data and with out selecting each
| inidividual cell to add up all the cells in column B if Column A
| equals "em". I am only asking this because I work with a couple
| managers that need the data to be in a specific format and wanted me
| to keep it update with automatic calculations at the bottom to sum all
| the data for each of the functions in column A.
|
Click to expand...

Thanks, worked like a charm.
 
I need to do something similar, but take the MEAN...Is there an "AVERAGEIF"
formula?
 
=SUMIF(A1:A11,"em",B1:B11)/COUNTIF(A1:A11,"em")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I need to do something similar, but take the MEAN...Is there an "AVERAGEIF"
| formula?
|
|
| | >
| > Column A Column B
| > em 5
| > mm 6
| > tnk 7
| > em 545
| > mm 45
| > tnk 78
| > tnk 746
| > tnk 457
| > mm 184
| > mm 154
| > em 157
| > mm 1557
| >
| > Above is a short example of an excel file I have andI was wondering if
| > there is a way without re-sorting the data and with out selecting each
| > inidividual cell to add up all the cells in column B if Column A
| > equals "em". I am only asking this because I work with a couple
| > managers that need the data to be in a specific format and wanted me
| > to keep it update with automatic calculations at the bottom to sum all
| > the data for each of the functions in column A.
| >
|
|
 
Back
Top