Excel 2007 : Counting values within a range exceeding a certain value

[sunil68]

I have "dates" in Col. A and about 15 values in Col. B corresponding to each date.
For example,
Row # Date Value
Row 1 - 1/1/2010 - 10
...............................
Row 15 - 1/1/2010 - 65
Row 16 - 1/2/2010 - 20
--------------------------------
Row 30 - 1/2/2010 - 25

I need to count the number of values exceeding a certain number corresponding to each date. For ex., for 1/1/2010, the value = 5 (5 times the number '50' was exceeded within the range Row 1-Row 15). For 1/2/2010, the value = 3 (3 times the number '50' was exceeded within the range Row 16-Row 30).

Can somebody provide me with a formulae in Excel 2007 to do this?

Thanks

[JBeaucaire]

You should change your profile to reflect xl2007, currently you're showing xl2003 in the upper right corner of your posts.

An xl2003 formula would be SUMPRODUCT().


Assuming you entered a data in K1 of 1/1/2010, you could put this formula in L1:

=SUMPRODUCT(--($A$1:$A$300=$K1),--($B$1:$B$300>50))

You can copy that formula down and it will adjust, so if you had the date 1/2/2010 in K2, L2 would collect the correct info for that date, etc.


In xl2007 you also have a function called COUNTIFS(), you'll have to read up on that, press F1 in Excel.

[sunil68]

Hi JBeaucaire,

Thanks a bunch for your help. It worked. I just had one last question related to my above problem. Corresponding to the 15 "Value" in Col. B, I have 15 "Station ID" in Col. C. I need to get a list of all "Station ID"s associated with the highest "Value"s among those 15 "Value"s for each "dates". While I have been able to pick up the highest value among those 15 values for each date, I cound not pick up the corresponding Station ID(s) associated with those highest values for each date. I have many dates in my database when the highest values for a particular date are shared by multiple Station IDs. For ex., on 1/1/2010 two Station IDs share the highest values of 65. So I would need to get a list of both Station IDs associated with the highest value (65). Hope it does not take too much of your time.

[JBeaucaire]

These problems typically require:

1) Addition of a helper column to watch your "report section" and index the rows that match your current criteria so that the matching rows can be brought into the report section with no array formulas, just simple lookup formulas.

GOOD: Never a performance hit, should peppy even on really big data sets.

BAD: Lots of formula, so the workbook can get noticeably larger, not something I typically care about. Helper columns are often unsightly but easy to just hide those columns, if desired.

2) Use of array formulas that do not require the helper column and can accomplish the same calcs anyway.

GOOD: No helper column to remember is there, especially if it is hidden

BAD: These work because each cell within the array formula is effectively doing ALL the same calcs the helper column did, but it's doing them 100s, if not 1000s of times all the time. In small doses, array formulas are nice. On large dataset, these formulas can slow your workbook down the point of unusability.

============
Post your sample workbook and I can then tell which method will work best.

[sunil68]

Thanks a lot for responding. Below is a sample of my data for 2010. The actual dataset has about 4500 rows in total. I have these data stored in separate work-sheets within the same work-book for each year beginning 1997 until 2010 and I intend to keep adding data for 2011 and beyond in the same work-book ni the future. My desired output is listed just below the sample data here.

Date Site ID Daily Max Concentration
4/1/2010 11-001-0041 0.047
4/1/2010 11-001-0043 0.051
4/1/2010 24-017-0010 0.054
4/1/2010 24-021-0037 0.05
4/1/2010 24-031-3001 0.051
4/1/2010 51-153-0009 0.058
4/1/2010 51-179-0001 0.056
4/1/2010 51-013-0020 0.053
4/1/2010 51-510-0009 0.05
4/1/2010 51-059-0018 0.054
4/1/2010 51-059-0030 0.058
4/1/2010 51-061-0002 0.060
4/1/2010 51-107-1005 0.055
4/1/2010 24-033-0030 0.052
4/1/2010 24-033-8003 0.054
4/1/2010 24-009-0011 0.055
4/2/2010 11-001-0041 0.05
4/2/2010 11-001-0043 0.057
4/2/2010 24-017-0010 0.055
4/2/2010 24-021-0037 0.057
4/2/2010 24-031-3001 0.057
4/2/2010 51-153-0009 0.06
4/2/2010 51-179-0001 0.053
4/2/2010 51-013-0020 0.058
4/2/2010 51-510-0009 0.055
4/2/2010 51-059-0018 0.055
4/2/2010 51-059-0030 0.059
4/2/2010 51-061-0002 0.06
4/2/2010 51-107-1005 0.058
4/2/2010 54-003-0003 0.057
4/2/2010 24-033-0030 0.057
4/2/2010 24-033-8003 0.055
4/2/2010 24-009-0011 0.058
================================

Desired Output Data
---------------------------------

Date Highest Daily Max Conc Site ID(s) associated with Highest Daily Max Conc
4/1/2010 0.06 51-061-0002
4/2/2010 0.06 51-153-0009, 51-061-0002
----------------------------------------------------------------------------------------------------------------------------------------
Thanks once again.

[JBeaucaire]

You have too many needs here for simple formulas, plus your length of data even makes arrays inappropriate.

I can see no peppy possibility here other than VBA.

Also, you uploaded an xl2007 file, your profile shows xl2003? I don't have xl2007, but you might be able to do this using the ADVANCED FILTER from column A to get a list of unique dates in a new column, then use the function COUNTIFS() to do your math.

[JBeaucaire]

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

[sunil68]

Atatched is the work-sheet for the year 2010 only. I have similar work-sheets for years 1997-2009 as part of one single work-book and plan to keep adding similar work-sheets for future years as well. I did not upload the whole work-book containing work-sheets for other year due to file size constraint on this web-site. Thanks.