Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
How can I COUNT data in one column in Excel based on a range of values in another column?
Ok.. I have 2 columns of data. The first column is dates. There are multiple entries per date (so for example, 1/1/2011 may be repeated 20 times because there were 20 entries entered on that date). I am looking for blocks of information for certain weeks (so a range of 1/1/2011 - 1/7/2011). The second column has corresponding data for that date (so 1/1/2011 might have 253 in column B). I need to count how many times (or how many days), during a specific week, that a the column B data was between 0 or 180. So, I need to know how many times during week 1/1/2011-1/7/2011 (for example) a day had a value between 0 and 180. I'm doing this to see how many times, during any given week, a person made a call that lasted from 0-3 minutes, 3-6 minutes, 6-9 minutes, or 9+ minutes. The 180 is in seconds (which equals 3 minutes). So, is there a way to do this in excel and if so, how do I do that? I'm using Excel 2010. Thanks!!
2 Answers
- Anonymous5 years ago
There are two ways of doing this with excel. One is writing a VBA program the other is using the spreadsheet features. The algorithm for this merge is quite simple. Scan the list joining the date strings together. When the id changes write out the record and load the next one. Repeat untill done. We could do a variation of this in the spreadsheet. Take a copy of the data because the process mangles the list. Add a column that tests for the change in ID. On change set the cell to the date of the current line on no change concatinate the cell above with the date on the current line. Add a second column. It should test downward for change in ID. On change set the cell to a "X". It's just a mark to know which record to select. Copy the new columns and paste special to values. This is to freeze the data we pulled out so the sort won't break it. Sort the records on the column with the frozen marker then the ID. This should give you the data close to the form you want. A little more excel work should do it.
- 9 years ago
There are many ways to do this, and I will offer up one potential approach. First, add formulas in column C,D,E, etc that would test column B against the thresholds that you want. For example, Column C may have a formula such as: =IF(B1<180,1,0) and Column D's formula would be : =IF(B1<180,0,IF(B1<360,1,0)). So column C would count the number of times the call duration was less than 3 minutes, and Column D would count the number of times the call duration was between 180 and 360 seconds. Repeat this concept in columns E, F, etc based on the various time thresholds that you want. As for the summarization by week, there are many ways to accomplish this as well. You might want a column that simply finds the week number for the date in question : =WEEKNUM(A1). Then it is a simple matter of doign a pivot table or SUMIF formula to aggregate the data. For example, if Column E contained the week number for each of the data entires, and Coulmn H listed the week numbers sequentially, your formula would be: =SUMIF(E:E,H1,C:C) This would return the number of times that a call was <180 seconds within the given week number.
Sorry if this is hard to follow, it would be much easier to provide a sample spreadsheet.
