For the Spreadsheet of Horror

I thought that I had figured this out before.

I need a count of rows where one and only one cell in each row contains a 1 in a particular column.  Example (b is a blank cell):
    A B C
1  1 b b b
2  b 1 1 b
3  1 b b b

I want to sum all of column A (then B and C) for each row where [column,row] is 1 but all other cells on that row are blank.  I know SUMIFS is the magic function, but the criteria have me a bit stumped.

=SUMIFS(A1A3,A:A,1,B:B," ","C:C," ")

Add a total column

   A B C D
1  1 b b 1
2  b 1 1 2
3  1 b b 1

=sumifs(A1:A4,D1:D4, 1)

Not quite.  I need another column for each of the columns to test: the total - the column I am trying to make unique.


   A B C D E
1  1 b b 1 0
2  b 1 1 2 2
3  1 1 b 2 1

=SUMIFS(A1:A4,E1:E4,0)


Ifinished this and realized what I really needed was the sum of deaths for each cause of death where only one weapon was used.  So

   A  A B C D E
1  29 1 b b 1 0
2  5  b 1 1 2 2
3  10 1 b 2 1 0



This required adding columns for total columns with a weapon used, then a count of columns - each weapon category. Then for each category of weapon:

=SUMIFS(A2:A6,G2:G6,0)

where A2:A6 is the dead per incident and G2:G6 is the column of weapons total - the count of that particular weapon. 0 means no weapons other than the one for which I am totalling dead.

And yes, I am beating Escel into doing what a RDBMS does.

Excel Has More Functions Than Any One Person Likely Ever Learn

Like all the Smalltalk classes.  Not sure if the words in this post will help other Excel users find this, but it's worth a try.

I have this spreadsheet where I subtotal each decade's mass murders.  One column contains the count of the decade's rows, in this example, rows 2-5.  The count is in in B6.  The row count is computed with =ROWS(B2:B5).  I would like to do an operation on just the cells in that decade's rows, but not including the subtotal row's cell.  The SUMIF and SUMIFS functions are very useful, if you know the range of rows and the column.  If you are doing an operation in your subtotal row, the current row number =ROW().  Put the decade's starting row number in AH6 with the formula =ROW()-B6.  Put the column  number for AD (from which I want to construct a range for the SUMIF function, in AJ6.  To get the contents of that cell, =INDIRECT(ADDRESS(AH6,AJ6)).  ADDRESS converts row and column number to a cell address and INDIRECT returns the contents of that cell.  Best of all, I can copy this formula (or a calculated range) into every subtotal row, without making it specific to that row.