Excel/Need a formula that includes ALL data in Column C
QUESTION: Hi "Tom",
Here's a formula for "Round 1" stats in Column C. =countifs(c4,"<1",c2,"<="&c1)
I didn't write this formula but I'm told it says what I want it to say.
"If cell c4 is less than 1 & cell c2 is less than or equal to cell c1...ADD 1 to the running total in cell P15.
I need that formula to include ALL the pertinent info in Column C down to row 800.
The "Round 2" formula would be =countifs(c9,"<1",c7,"<="&c1) And so on. To work, the formula would be applied DOWN Column C. I don't know how to write a formula that will ADD all of the "sets" of data for this category in Column C. I know how to write a simple formula that will check for all the 4's in Column C. (Formula in cell P8)
I don't know how to write the needed formula, because it has multiple parts to it. My formula seems to need 3 parts. 1) if c4 is less than 1 2) and c2 is <= to cell c1 3) then Add 1 to the running total in the "given" cell. The data numbers being used are DIRECTLY to the right of the titles "Par" "Score" "Putts". I need the data from the 160 "sets" to be ADDED to the given cell in the April table. I suppose I could do it the hard way (maybe), and write something like this.
=countifs(c4,"<1",c2,"<="&c1) + =countifs(c9,"<1",c7,"<="&c1) +...
That probably wouldn't even work. I don't know how to add formula to formula. So, I know it's much easier to make it one formula that ADDS all the data.
I think that's about it. Let me know if you need clarification.
from what I can see from your description you have 32 rounds each consisting of 5 cells (counting the blue separator cell). I get the 32 from your reference to 160 so 160/5 = 32.
Given that, then I would use this formula:
The MOD part of the formula is to make sure the formula is only applied/conditions checked once per round.
countif and countifs is designed to work row by row as in a database - but your data is not organized that way so I don't see countif as the correct formula.
---------- FOLLOW-UP ----------
QUESTION: Hi Tom,
1) There 800 rows. (160*5)
2) I used the "countifs" for counting all the 5's, 4's, 3's, 2's etc in Column C. (b:b,"score",c:c etc.)
3) Excel would not let your formula go as is. They made me take out the , after the =0), and <1),
4) Your formula resulted in the # 1. No matter what I input in the data cells. They were blank = 1. If I puts some numbers in = 1. I came to you because I have almost zero formula skill. When I look at your formula, the part AFTER =0),-- looks just fine to me. I don't know if it's written properly, but it seems to say what I'm looking for. The first part I don't understand. I see a +1 which is what needs to be ADDED to P15 if the criteria in the formula is met. I don't have a clue why there is a 5 there. (Every 5 rows????) Let me write a definition of what I want and give you examples of what would be in the cells and the outcomes. Hopefully, that will help.
If cell C4 does NOT have a 1 in it and cell c2 is less than or equal to cell c1, ADD 1 to cell P15. I could write the formula manually for each set, but doing that 160+ times doesn't seem like much fun. Ex. exchange c4 with c9. Exchange c2 with c7. Exchange c9 with c14. Exchange c7 with c12. I don't know how to add all 160+ of those formulas and get them to work AND don't want to. I hope I'm making it clear that the "answer" from the data in C2:C4 and the "answer" from the data in c7:c9 and the "answer" from the data in c12:c14 etc. ALL need to be ADDED to the running total in P15. (All the way down to row 800) I will break the formula into pieces depending on how many "rounds" I play in April, May, June etc.
Here are 10 examples and what should happen with the proper formula.
Cell C1 is always 4.
1) There is a 4 in C2. There is a 2 in C3. There is a 1 in C4. Since there is a 1 in C4, no action is taken.
2) There is a 4 in C7. There is a 1 in C8. There is NOT a 1 in C9. Since there is NOT a one in C9 and C7 is <= to C1, 1 is ADDED to P15.
3) There is a 4 in C12. There is 0 in C13. There is NOT a 1 in C14. Since there is NOT a one in C14 and C12 is <= to C1, 1 is ADDED to P15.
4) There is a 3 in C17. There is a 1 in C18. There is a 1 in C19. Since there is a 1 in C19, no action is taken.
5) There is a 5 in C22. There is a 2 in C23. There is NOT a 1 in C24. Since there is NOT a 1 in C24 we look to see if C22 is <= than C1. It is not, so no action is taken.
6) There is a 6(ouch!) in C27. There is a 3 in C28. There is NOT a 1 in C29. Since there is NOT a 1 in C29 we look to see if C27 is <= to C1. It is not, so no action is taken.
7) There is a 3 in C32. There is a 0 in C33. There is NOT a 1 in C34. Since there is NOT a 1 in C34, we look to see if C32 is <= to C1. It is, so 1 is ADDED to P15.
There aren't any more possibilities for a 1 to be ADDED. But I'll give you three more data inputs anyway.
8) There is a 2 in C37. There is a 1 in C38. There is a 1 in C39. With a 1 in cell 39, no action is taken.
9) There is a 4 in C42. There is a 1 in C43. There is NOT a 1 in C44. Since there is NOT a 1 in C44, we check to see if C42 is <= to C1. It is, so 1 is ADDED to P15.
10) There is a 1 in C47. (Yea!! I just ACED a par 4) There is a 0 in C48. There is a 1 in C49. Since there is a 1 in C49, no action is taken.
The total in P15 from the data in C2:C50 should be.......4
I've noticed people get attracted to the A Column for some reason. It should not be involved in your calculations. Just what goes on in Column C from C1:C800. Man, I wish I could write this crap on my own.
Good luck Tom.
If anything more needs clarification, please let me know.
If I can send my spreadsheet somewhere for you if needed, let me know.
the formula I posted was taken out of an excel cell and worked fine - so anything you say after that is pretty much meaningless to me. So if you want to send me your workbook I will take a look