SUMing an IF formula using strings
Posted by Mike Winters on December 04, 2001 10:05 AM
I have the following formula in my spreadsheet:
=IF(AND(OR(G4="2LT",G4="1LT",G4="CPT",),R4="YES"),1,"")
It scans G4 for the occurence of 3 strings. If any of them match, AND cell R4 also says "YES", a 1 appears in the cell (this formula is in A4). I have 4 columns and over 80 rows of this formula (each column searches for different strings). At the bottom of each column is a simple SUM formula adding all the 1s in that column, which gives me the number of people of a certain rank who have a certain qualification. What I want to do is take out those four columns. How would I modify the formula so instead of just checking cell G4 and R4, it checks cells G4 to G110, R4 to R110, and returns the total number? Thanks in advance to any help you can give.
Sincerely,
Mike Winters
United States Army
Posted by IML on December 04, 2001 10:18 AM
I'm not sure I fully follow, but give the following a try
=SUMPRODUCT((G4:G110={"2LT","1LT","CPT"})*(R4:R110="yes"))
and see if it produces the desired result.
Good luck & thank you for your service.
Posted by Mike Winters on December 04, 2001 10:30 AM
That did it
That worked perfectly! Thank you for your (extremely quick) help. And ypu're welcome. Being appreciated makes all the things we do worth it.
Sincerely,
Mike Winters
United States Army
