Armed Polite Society
Main Forums => The Roundtable => Topic started by: Hawkmoon on May 23, 2017, 09:28:47 AM
-
I found a glitch in my spreadsheet tabulating mass shootings and terrorism killings. I have a column for incidents involving guns, and I have a coumn for incidents in gun-free zones. I need to set up a formula that enters into another column a number if the incident involved guns AND took place in a gun-free zone. How do I nest the two IF statements so that
IF F2 is yes AND IF G2 is yes, the number goes into column N but if either F2 or G2 is no or blank, nothing goes into Column N?
-
This is where the Countif may well be what you want.
Take a look at this...
https://exceljet.net/formula/count-if-two-criteria-match
-
Good idea, and I wasn't familiar with that function, but I don't think it does what I need. My 'N' column is where I want to enter the number of people killed IF guns were involved AND if the incident was in a gun-free zone. What the COUNTIFS function seems to do is go through a range and tell me how many incidents meet both criteria -- but not take the number killed from column 'F' and copy it to column 'N'.
-
Got it. Have to nest the AND function after the IF function.
=IF(AND(F2="Yes",H2="Yes"),D2,"")
I didn't know I could do that. The day is not a total loss -- I learned something, and it's not even noon yet.
-
There is the SUMIF formula, but I don't recall if you can use two variables or not. You might be able to use it in stages. If one is true, copy the number to a separate column, else zero. Use a sumif for the second variable off the separate column. You might be able to put one IF statement inside another, but that would mean large formula and staging it might be easier to troubleshoot later. You can hide the columns you don't need displayed.
There are pivot tables also that can do multivariable sorting, but I never got much practice using them so I couldn't tell you much.
If has been 5 or more years since I had to deal regularly with large spreadsheets for environmental reporting. I am rusty on that stuff.
Edit: Sounds like you got it.
-
Whoa. Nice. That's far beyond my capabilities with Excel.
-
Beyond mine, too. In fact, as I discovered when I entered the latest New Tampa Islamist shooting info into my spreadsheet this morning, even though I've had the spreadsheet up and running for over a year -- I've been doing it wrong for over a year, and it was screwing up my compiled statistics. Now that I noticed the discrepancy, I think I have it fixed.
Until I notice the next glitch.
Which is another way of obliquely acknowledging that I'm already in over my head.
-
Glad I read all the posts before showing the "AND" solution. "OR" is available and just as useful as well for things similar to this.
-
There is the SUMIF formula, but I don't recall if you can use two variables or not.
That would be the SUMIFS formula.