Armed Polite Society
Main Forums => The Roundtable => Topic started by: Hawkmoon on April 12, 2017, 04:24:26 PM
-
I have a spreadsheet for tracking mass shootings. I started compiling it a couple of years ago for possible use in trying to persuade my town's government to repeal a very restrictive, anti-gun town ordinance. That attempt was a total failure -- after meeting with the full governing board plus the town attorney, the result was that, according to the Mayor, "That's all very interesting, but ... wed on't like guns."
But I keep up the spreadsheet, and I now have columns for tracking Islamist terror attacks in addition to school and other mass shootings. But I'd like to figure out formulas ("formulae"?) for keeping running totals of each column, but -- for example -- not all shootings involve Islamist terror, and not all Islamist terror attacks involve shooting. I don't know how to set up a formula to add all values in Column E where the value in Column C is "Yes," but exclude values in Column E if the corresponding value in Column C is "No" or blank.
Can anyone guide me to setting this up?
-
Uh... det cord?
I know what you're trying to do... but I don't know how to do it. I THINK you want to use a COUNTIF formula...
Try searching on COUNTIF
-
Uh... det cord?
I know what you're trying to do... but I don't know how to do it. I THINK you want to use a COUNTIF formula...
Try searching on COUNTIF
That's a good one -- not what I was looking for, but possibly useful. COUNTIF will return a count of the number of incidents where I enter "Yes" for "gun-free zone" (for example), but it doesn't total the number of people killed in gun-free zones.
I'll keep studying that one. Maybe I'll find a link to related functions. Thanks.
-
If you want a total instead of a count try sumif instead of countif
Sent from my iPhone using Tapatalk
-
You might need to use both count if and sum if depending on how you want the data to parse.
Sent from my SCH-I545 using Tapatalk
-
Thank you both. I think between those two I can accomplish what I hope to do.
-
If ya need more help, I can show ya how to get there from here. Skype or PM and I will give ya my number or call you.
-
There is also a function "SUMIFS". Note the plural. You can have it sum a column (or range, or row,...) based on more than one criteria. One of my favorites. That and "VLOOKUP".
-
I have used "VLOOKUP" in the past, quite effectively. My coworkers thought it was black magic -- there were literally some "How did you DO that?" comments.
-
I have used "VLOOKUP" in the past, quite effectively. My coworkers thought it was black magic -- there were literally some "How did you DO that?" comments.
If you really want to astound them, show them how to lookup to the LEFT of the column. The technique is using a compound formula with "INDEX", and "MATCH". Use Index to declare the area to pick from, and use Match one or 2 times to select the which row in the column to reference and what column to pull the result from. So, =INDEX(range, MATCH, MATCH). Not the best explanation, but if you play with that structure a bit I'll bet you'll figure it out.