Need Count Based on Date Range

D

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
I need a formula to sum the number of valid data points based on a specified date range.

I was able to compute the total sum for a specified date range within a larger data set and avoid any errors within the data set (range) using the SUM formula as shown:

Code: 
=SUM(IF((Daily!$A$733:$A$1097<=D762+7)*(Daily!$A$733:$A$1097>=D762),IF(NOT(ISERROR(Daily!$B$733:$B$1097)),Daily!$B$733:$B$1097,0)))
Now I need to do the same to get the count of the number of data points that made the sum from within a larger data set but just can't seem come up with what I need.

The following formula at E768 works perfectly when I confine the range to just the dates used in the sum formula above. However I need to correlate the number of data points with the date range consistent with the SUM formula above.

Code: 
=COUNTIF(B762:B768,">0")
For resons beyond this scope, the #DIV/0! error cannot be avoided within the dataset. So please do not suggest fixing what causes the error in first place.

A sample of the data is shown below:


Daily

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 89px"><COL style="WIDTH: 73px"><COL style="WIDTH: 22px"><COL style="WIDTH: 74px"><COL style="WIDTH: 94px"><COL style="WIDTH: 94px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">759</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/27/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.80</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">760</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/28/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.60</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">761</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/29/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,014.40</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">762</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/30/2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-FAMILY: Verdana">1,014.40</TD><TD></TD><TD style="TEXT-ALIGN: right">1/30/2011</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">763</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/31/2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-FAMILY: Verdana">1,014.10</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">764</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/1/2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-FAMILY: Verdana">1,016.10</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">765</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/2/2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-FAMILY: Verdana">1,019.50</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">766</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/3/2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-FAMILY: Verdana">1,019.90</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">767</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/4/2011</TD><TD style="BACKGROUND-COLOR: #ffcc00; FONT-FAMILY: Verdana">#DIV/0!</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">768</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/5/2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc00; FONT-FAMILY: Verdana">1,020.90</TD><TD></TD><TD style="TEXT-ALIGN: right">6,104.90</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center; COLOR: #800080">1017.483</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">769</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/6/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,022.00</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">770</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/7/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,021.00</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B767</TD><TD>=8/0</TD></TR><TR><TD>D768</TD><TD>{=SUM(IF((Daily!$A$733:$A$1097<=D762+7)*(Daily!$A$733:$A$1097>=D762),IF(NOT(ISERROR(Daily!$B$733:$B$1097)),Daily!$B$733:$B$1097,0)))}</TD></TR><TR><TD>E768</TD><TD>=COUNTIF(B762:B768,">0")</TD></TR><TR><TD>F768</TD><TD>=D768/E768</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Can someone please help!
 
Sort by date Sort by votes
dwgnome said:
Not sure if I need to start a new thread, but it is related to the original thread and the original post information /setup.

For the cases when there is no data within the corresponding specified date range or if there are no dates in the specified date range the AVERAGE formula displays #DIV/0!. To avoid the display, I could use ISERROR(formula,"",formula) approach, however, I am looking for a way to use a counter of some sort to identify those cases and have either null ("") or "No Data" displayed. I attempted the following before the AVERAGE portion but it does not work.

Code: 
IF(COUNT(IF(MONTH('Daily NG Btu'!$A$6:$A$1145)=MONTH(D5),YEAR(A5)))

While this formula addresses the monthly average, the layout is similar to the weekly average from Aladin's and T. Valko's last post. Can somebody come up with a solution for the weekly average formula (see Aladin or T. Valko's formula earlier in this post) using either COUNTIF, FREQUENCY or some other way to check for the presence of data and divert the message to state "No Data" for these two cases?

Your help would be most appreciated. I am using Excel 2003.
Click to expand...

Example: Control+shift+enter, not just enter...
Rich (BB code): 
=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
  AVERAGE(IF(Daily!$A$733:$A$1097>=D762,
   IF(Daily!$A$733:$A$1097<=D762+7,
   IF(ISNUMBER(Daily!$B$733:$B$1097),
   IF(Daily!$B$733:$B$1097>0,Daily!$B$733:$B$1097)))))))

Custom format the formula cell e.g., as: [=0]"";General
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Thanks Aladin, that helps prevent the error messages. I tried to put "No Data" with and without quotes instead of the zero but when the condition is met, it reverts to #NA instead of the intended message. I tried to put the "No Data" in another cell and reference that cell, still it did not work.
 
Upvote 0
dwgnome said:
Thanks Aladin, that helps prevent the error messages. I tried to put "No Data" with and without quotes instead of the zero but when the condition is met, it reverts to #NA instead of the intended message. I tried to put the "No Data" in another cell and reference that cell, still it did not work.
Click to expand...

Custom format the formula cell as:

[=0]"No Data";General

Is this admissible?
 
Upvote 0
Oh, I got it to work, I forgot to put the zero back.

Thank you so much, brilliant solution.:)
 
Upvote 0
dwgnome said:
Not sure if I need to start a new thread, but it is related to the original thread and the original post information /setup.

For the cases when there is no data within the corresponding specified date range or if there are no dates in the specified date range the AVERAGE formula displays #DIV/0!. To avoid the display, I could use ISERROR(formula,"",formula) approach, however, I am looking for a way to use a counter of some sort to identify those cases and have either null ("") or "No Data" displayed. I attempted the following before the AVERAGE portion but it does not work.

IF(COUNT(IF(MONTH('Daily NG Btu'!$A$6:$A$1145)=MONTH(D5),YEAR(A5)))

While this formula addresses the monthly average, the layout is similar to the weekly average from Aladin's and T. Valko's last post. Can somebody come up with a solution for the weekly average formula (see Aladin or T. Valko's formula earlier in this post) using either COUNTIF, FREQUENCY or some other way to check for the presence of data and divert the message to state "No Data" for these two cases?

Your help would be most appreciated. I am using Excel 2003.
Click to expand...
If you want a blank instead of the error then the formula will be a bit long...

Still array entered.

=IF(ISERROR(AVERAGE(IF(Daily!A733:A1097>=D762,IF(Daily!A733:A1097<=D762+7,IF(ISNUMBER(Daily!B733:B1097),IF(Daily!B733:B1097>0,Daily!B733:B1097)))))),"",AVERAGE(IF(Daily!A733:A1097>=D762,IF(Daily!A733:A1097<=D762+7,IF(ISNUMBER(Daily!B733:B1097),IF(Daily!B733:B1097>0,Daily!B733:B1097))))))

Another option is to put the basic formula in one cell and then test that cell for the error:

Enter the array formula in cell A1:

=AVERAGE(IF(Daily!A733:A1097>=D762,IF(Daily!A733:A1097<=D762+7,IF(ISNUMBER(Daily!B733:B1097),IF(Daily!B733:B1097>0,Daily!B733:B1097)))))

Then, enter this formula in B1:

=IF(ISERROR(A1),"",A1)
 
Upvote 0
Hi Biff,

I did not see your post until today. Your idea using the ISERROR(formula,"",formula) would work, but like you mentioned it gets big and clumsy. As I stated in my original post, I was hoping to avoid that.

Unfortunately, I cannot use your substitution method because that would only work for the first value not when I copy down the formula. The formula depends on having the first of each week (52 in all) in the column to the left of the formula. In this situation, I am not wanting to use a helper column either.

Your assistance is always appreciated.
 
Upvote 0
dwgnome said:
Hi Biff,

I did not see your post until today. Your idea using the ISERROR(formula,"",formula) would work, but like you mentioned it gets big and clumsy. As I stated in my original post, I was hoping to avoid that.

Unfortunately, I cannot use your substitution method because that would only work for the first value not when I copy down the formula. The formula depends on having the first of each week (52 in all) in the column to the left of the formula. In this situation, I am not wanting to use a helper column either.

Your assistance is always appreciated.
Click to expand...
Yeah, not a lot of good options for this one if you absolutely need a blank returned instead of any errors.

Thanks for the feedback!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
462
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
364
esben
E
V
  • Question
Issue with scraping with VBA
Replies
1
Views
539
John_w
J

Forum statistics

Threads
1,216,098
Messages
6,128,812
Members
449,468
Latest member
AGreen17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top