| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 30423 | 2003-02-19 01:14:00 | Excel formula help reqd. | Susan B (19) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 122343 | 2003-02-19 01:14:00 | In an Excel spreadsheet I have seven columns for each day of the week (eg B1=Sunday, C1=Monday, etc) and in Row 2 I want to put either a X or a Y to indicate a task done. In Cell I2 I would like to have a formula that will count up the number of cells with Y and multiply those cells by 5 for each one. So if there were four days with a Y then the total would be 20. I can figure out an IF formula: =IF(B5="y",5,0) for one cell but want a formula for adding up the seven cells in one go. Hopefully someone can understand my crude explanation and be able to help. :-) Using Excel 97/Excel XP. |
Susan B (19) | ||
| 122344 | 2003-02-19 01:18:00 | Hi Susan, I think the function you want is Count. Find it in the Statistical group. |
Heather P (163) | ||
| 122345 | 2003-02-19 01:41:00 | Hi Susan, the easiest way is to use Countif. The following exmple counts the number of Y's in cells a1 to a3, then multiplies by 5. eg: =COUNTIF(A1:A3,"Y")*5 cheers Parry |
parry (27) | ||
| 122346 | 2003-02-19 01:44:00 | The function COUNT only deals with numbers. To count other values use COUNTA or COUNTIF | Elephant (599) | ||
| 122347 | 2003-02-19 02:58:00 | Thank you everyone. :-) Parry, your formula is spot on and just what I wanted. Have not come across this one before and would have taken days to figure it out without your example. Many thanks. :-) |
Susan B (19) | ||
| 122348 | 2003-02-19 03:34:00 | Excellent, glad you have sussed it. Theres also a similar one for summing under different conditions - SUMIF. It's a goody as well :-) | parry (27) | ||
| 122349 | 2003-02-19 07:15:00 | Apologies to Heather_P. I re-read my post and looking at it again it seems a little snarky as it were. I was trying to put Susan_B on the right track. You were on a very good track anyway. |
Elephant (599) | ||
| 122350 | 2003-02-19 08:48:00 | Hi Elephant, I looked at the post, thought "that looks a bit like the count function", shot into Excel, checked quickly (I was about to do something else) and posted. Then I looked at Excel again, thought "Drat, it should have been one of the other two" but figured it was close enough to point Susan (a knowledgable person who doesn't need too much hand holding) in the right direction. Then I disappeared to whatever it was that was I was avoiding by visiting here. Later the two of you set Susan right. Sorry Susan, we don't want to make computers too easy for you :D |
Heather P (163) | ||
| 122351 | 2003-02-19 11:02:00 | Give this site a try it is EXCELlent www.mrexcel.com |
seacrest (2893) | ||
| 122352 | 2003-02-19 11:08:00 | one thing comes to mind. $35 is far TOO MUCH pockey money for someone. You have two options: a) give them less b) give it to me :D |
-=JM=- (16) | ||
| 1 | |||||