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.
Clayton:
ReplyDeleteIt 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!
Wayne: You are exactly right. The energy required to convert this to Access is time and energy that I do not have.
ReplyDeleteWhat I seem to have made work is:
ReplyDelete1) 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.
Clayton:
ReplyDeleteHow 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.
Wayne: I do not see your email address. My firstname@firstnamelastname.com.
ReplyDeleteThere 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.