98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Urgent formula help needed please
Urgent formula help needed please
Resolved · Urgent Priority · Version 2007
Katy has attended:
Excel Advanced course
PowerPoint Intermediate Advanced course
Urgent formula help needed please
Good morning
I need help with a formula, I think it needs to be an IF formula but I'm not sure.
In column A I have the number of days worked in a year. In column B, I need to look at the no of days in column A and calculate number between 41 and 120. In column C, I need to look at the no days in column A again and calculate the no of days after 121 (if there are any).
I probably haven't explained that very well! Any help would be greatly appreciated :-)
RE: Urgent formula help needed please
Hi Kate
You did explain it well. There are 2 methods that come to mind. One is to use a COUNTIFS function and the other is to use a Pivot Table.
I've made up some sample data in Column A so you can see which method is more practical
Countifs Method
To calculate 41-120
=COUNTIFS($A$2:$A$16,">40",$A$2:$A$16,"<121")
To calculate >120
=COUNTIF(A2:A16,">120")
Days
46
74
32
94
152
177
198
112
10
26
50
113
62
96
77
PivotTable method
With cursor in A1 choose Insert, Pivot table
Choose existing worksheet cell eg E5
Row Headings - Days
Value - Days - Summarize as Count
Then group the resulting data as follows (right click any value in the pivot table and choose Group)
Starting at 41
Ending at 120
By: 79
This is how the Pivot table displays:
Row Labels Count of Days
<41 3
41-120 9
>120 3
Grand Total 15
The Pivot table doesn't involve writing formulas but will need refreshing if data changes.
Hope that helps.
Doug Dunn
Best STL
Fri 6 Dec 2013: Automatically marked as resolved.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Manually wrapping textTo manually wrap text, use the shortcut key Alt+Enter. |