57896

Excel SUMIF formula needed

Question:

I'm looking for a SUMIF formula which adds up all values of a column if the row meets a certain criteria. The criteria is that it needs to match either of the 3 categories (A, B or C). My data looks like this:

A 5 4 B 32 4 B 12 6 B 4 7 A 21 342 C 2 2 C 1 1 A 3 5 A <total A> <total A> etc B <total B> <total B> etc C <total C> <total C> etc

The formula should work when dragged horizontally.

<img alt="Screenshot of an example table" class="b-lazy" data-src="https://i.imgur.com/5HhGQpN.png" data-original="https://i.imgur.com/5HhGQpN.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

Thanks in advance for the help!

Answer1:

@Mike Meinz gave you the right answer. Formula could be simplified like the following:

In Column B totals for criteria A, B and C can be calculated as following:

=SUMIF($A1:$A8,"A",$B1:$B8) =SUMIF($A1:$A8,"B",$B1:$B8) =SUMIF($A1:$A8,"C",$B1:$B8)

Note: absolute reference ($) is used to make the value stay the same if cell is dragged horizontally, as per your requirement.

In case you would like to calculate the totals of <strong>current</strong> column after the cell was dragged (rather than <strong>fixed</strong> one, i.e. column B in this example), modify the formula like:

=SUMIF($A1:$A8,"A",B1:B8)

Correspondingly, similar syntax can be applied to Column C. Regards,

Answer2:

In Column B

=SUMIF($A$1:$A$8,"=A",B$1:B$8) =SUMIF($A$1:$A$8,"=B",B$1:B$8) =SUMIF($A$1:$A$8,"=C",B$1:B$8)

In Column C

=SUMIF($A$1:$A$8,"=A",C$1:C$8) =SUMIF($A$1:$A$8,"=B",C$1:C$8) =SUMIF($A$1:$A$8,"=C",C$1:C$8)

Recommend

  • Saas with database per client - single point for entry for services. Performance overhead?
  • Android: avoid calling onCreate() when back from another activity
  • GridView : Freezing First Columns and Freezing Column's Headers
  • Why doesn't a fixed-width element take up the space beside a floated element?
  • Reading on QRCodes on iOS with AVCaptureSession — alignment issues?
  • Key-aware consumers in RabbitMQ
  • Change scroll direction at Anchor point
  • How can i make infinite flowing background with only CSS?
  • How to center the y-axis label horizontally in Highcharts?
  • CSS overflow with long URL
  • iOS Safari image rendering issue
  • How do I add margin between divs that are set to display table-cell?
  • NetBeans moving tabs navigation to the right
  • How to design resolution independent CSS elements?
  • different results with and without SSE ( float arrays multiplication)
  • jquery datatables FixedColumns not defined
  • Shadows for Instantiated Game Object bug?
  • How to control Trigger state (Pause, Play) using code (not just buttons)
  • JSON data through JS/AJAX into PHP
  • Jquery UI Sortable, move item automatically
  • CSS how to fix an element to scroll horizontally with the page but not vertically?
  • calculating number of bytes of each row in an image
  • Unable to play media with vlc ocx
  • SQL: Getting the physical size of a subset of a table
  • Using MouseListener to select a range of cells in a grid
  • pip in virtualenv gets ConnectTimeoutError
  • Spring Cloud Microservice Architecture Confusion
  • Marklogic : Query response time is very high
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Arrays break string types in Julia
  • Comma separated Values
  • File upload with ng-file-upload throwing error
  • WPF Applying a trigger on binding failure
  • Java static initializers and reflection
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • How to load view controller without button in storyboard?