You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Need a formula that includes ALL data in Column C

Advertisement

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)

=countifs($b:$b."score",$c:$c,"=4"

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.

Thanks

B

ANSWER: B,

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:

=SUMPRODUCT(--(MOD(ROW($C$4:$C$159)+1,5)=0),--($C$4:$C$159<1),--($C$2:$C$157<=$C$1))

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.

--

Regards,

Tom Ogilvy

---------- 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.

Thanks

BL

BL,

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

twogilvy@msn.com

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 1 | Clarity of Response = 1 | Politeness = 1 |

Comment | My question was, "meaningless" to him. Great communication skills, not. There was a typo by me and he was quite negative. Not helpful. |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.