Friday, March 6, 2020

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


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:


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.


Wayne Johnson said...


It looks like you are trying to turn MS Excel into a poor-man's referential database. I think you would be far better served by using a database engine. Even Microsoft Access would be far better than Excel.

If you are comfortable with a SQL prompt, you could look at using PostgreSQL. This is a free, open-source database.

I can probably give you a (virtual) hand converting a spreadsheet into SQL insert statements.

Good luck!

Clayton Cramer said...

Wayne: You are exactly right. The energy required to convert this to Access is time and energy that I do not have.

Karl said...

What I seem to have made work is:

1) Create a column which contains the sum of each row. (I used "E" so I'd have a space between the sum and the data.)

2) At the bottom of each data column, use =SUMIFS(A4:A9,$E4:$E9,"<2")

To test, I added a row with "1" in column B and a row with "1" in column C.
Further testing shows this seems to work.

This was in Libre Office, but I suspect Excel will behave the same way.

Wayne Johnson said...


How many rows and columns are in this horrific spreadsheet?

I may be able to convert them into SQL (to load into Access or another RDBMS) without too much fuss or drama.

I think you can see my email address if you want to contact me, but if not, throw a comment and I will make out of band contact.

Clayton Cramer said...

Wayne: I do not see your email address. My

There are 5 worksheets. The big one goes to BB:637, but some columns are the funny total - items above, and some rows are subtotals or totals.