Excel dynamic range auto sum based on month

V

Valde_Edius

New Member
Joined
Jul 13, 2011
Messages
6
So I have a list of expenses along with their date and I want to create a field that will sum them up automatically based on month.
It looks like:
Date | Amt
-------------------
1/1/11 | $20.00
1/13/11 | $350.00
2/7/11 | $40.00
2/14/11 | $50.00
3/4/11 | $10.00

I then have a field that tries to pull the sums of these areas using the following formula:
Code: 
=SUMPRODUCT((MONTH(Date)=2)*D4:D9)
Where Date is the dynamic range of the date column and D4:D9 is the Amt. the '2' will correspond to February.

Now this works great, it does exactly what I want it to and each month can be counted up, but if I create a dynamic range for the Amt and use the formula like:
Code: 
=SUMPRODUCT((MONTH(Date)=2)*Amt)
It no longer works at all.

Is there a way I could create a completely dynamic range so I don't have to update the range manually everytime I want to view my expenses? I considered the possibility of using COUNT to give me a number representing the number of cells with data in them and somehow using that to fill in the second number on the range, but I do not know how to do that using excel.
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Try this array formula (use Ctrl+Shift+Enter and not only Enter):


Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Date </TD><TD>Amt</TD><TD style="TEXT-ALIGN: right"></TD><TD>Month</TD><TD>Amt</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1/1/2011</TD><TD style="TEXT-ALIGN: right">$20.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Jan</TD><TD style="TEXT-ALIGN: right">$370.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">1/13/2011</TD><TD style="TEXT-ALIGN: right">$350.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Feb</TD><TD style="TEXT-ALIGN: right">$90.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">2/7/2011</TD><TD style="TEXT-ALIGN: right">$40.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Mar</TD><TD style="TEXT-ALIGN: right">$10.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">2/14/2011</TD><TD style="TEXT-ALIGN: right">$50.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">3/4/2011</TD><TD style="TEXT-ALIGN: right">$10.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(D2=TEXT($A$2:$A$6,"mmm"),$B$2:$B$6))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

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

Markmzz
 
Upvote 0
Or try this version for dynamic range:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Date </TD><TD>Amt</TD><TD style="TEXT-ALIGN: right"></TD><TD>Month</TD><TD>Amt</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1/1/2011</TD><TD style="TEXT-ALIGN: right">$20.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Jan</TD><TD style="TEXT-ALIGN: right">$370.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">1/13/2011</TD><TD style="TEXT-ALIGN: right">$350.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Feb</TD><TD style="TEXT-ALIGN: right">$90.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">2/7/2011</TD><TD style="TEXT-ALIGN: right">$40.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Mar</TD><TD style="TEXT-ALIGN: right">$240.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">2/14/2011</TD><TD style="TEXT-ALIGN: right">$50.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">3/4/2011</TD><TD style="TEXT-ALIGN: right">$10.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">3/5/2011</TD><TD style="TEXT-ALIGN: right">$30.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">3/6/2011</TD><TD style="TEXT-ALIGN: right">$200.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(D2=TEXT(INDIRECT("A$2:A$"&COUNTA(A:A)),"mmm"),INDIRECT("B$2:B$"&COUNTA(B:B))))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>

Markmzz
 
Last edited:
Upvote 0
I used
Code: 
=SUM(IF(I3=TEXT(INDIRECT("C$4:C$"&COUNTA(C:C)),"mmm"),INDIRECT("D$4:D$"&COUNTA(D:D))))
and it returned the sum of everything from D4:D... Is there a way of making it capable of picking up the month from this information and using that to calculate the sum of the values for that month only?
 
Upvote 0
Valde_Edius said:
I used
Code: 
=SUM(IF(I3=TEXT(INDIRECT("C$4:C$"&COUNTA(C:C)),"mmm"),INDIRECT("D$4:D$"&COUNTA(D:D))))
and it returned the sum of everything from D4:D... Is there a way of making it capable of picking up the month from this information and using that to calculate the sum of the values for that month only?
Click to expand...

I didn't undertand why all data of column D was added.

Could you post an example of your worksheet?

Use the link below for helper.

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

Markmzz
 
Upvote 0
Excel Workbook
ABCD
1Gas Costs
2Total YTD66.673$234.56
3DateGallonsPriceSale
45/19/20118.249$3.759$31.01
55/26/201112.091$3.639$44.00
66/4/201111.037$3.459$38.18
76/18/201111.632$3.499$40.70
86/27/201110.288$3.409$35.07
97/8/201113.376$3.409$45.60
Gas
Excel 2010
Cell Formulas
RangeFormula
B2=SUM(B4:B9992)
D2=SUM(D4:D9999)
D4=IF(C4="","",C4*B4)
D5=IF(C5="","",C5*B5)
D6=IF(C6="","",C6*B6)
D7=IF(C7="","",C7*B7)
D8=IF(C8="","",C8*B8)
D9=IF(C9="","",C9*B9)
 
Upvote 0
Valde_Edius said:
Excel Workbook
ABCD
1Gas Costs
2Total YTD66.673$234.56
3DateGallonsPriceSale
45/19/20118.249$3.759$31.01
55/26/201112.091$3.639$44.00
66/4/201111.037$3.459$38.18
76/18/201111.632$3.499$40.70
86/27/201110.288$3.409$35.07
97/8/201113.376$3.409$45.60
Gas
Excel 2010
Cell Formulas
RangeFormula
B2=SUM(B4:B9992)
D2=SUM(D4:D9999)
D4=IF(C4="","",C4*B4)
D5=IF(C5="","",C5*B5)
D6=IF(C6="","",C6*B6)
D7=IF(C7="","",C7*B7)
D8=IF(C8="","",C8*B8)
D9=IF(C9="","",C9*B9)
Click to expand...

Try this array formula (use Ctrl+Shift+Enter and not only Enter):

Note1: I create two dynamics names (myDate and myAmt).

Note2: Plan3 is the name of my worksheet.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c6efce; COLOR: #006100; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Gas Costs</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #f2f2f2; COLOR: #fa7d00; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Total YTD</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #f2f2f2; COLOR: #fa7d00; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">66.673</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #f2f2f2; COLOR: #fa7d00; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">$234.56</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #f2f2f2; COLOR: #3f3f3f; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Date</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #f2f2f2; COLOR: #3f3f3f; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Gallons</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #f2f2f2; COLOR: #3f3f3f; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Price</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #f2f2f2; COLOR: #3f3f3f; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Sale</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Month</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Amt</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5/19/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">8.249</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$3.76</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$31.01</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">May</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$75.01</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">5/26/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">12.091</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$3.64</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$44.00</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Jun</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$113.95</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6/4/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">11.037</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$3.46</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$38.18</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Jul</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$45.60</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6/18/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">11.632</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$3.50</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$40.70</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6/27/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">10.288</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$3.41</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$35.07</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">7/8/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">13.376</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$3.41</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #efefef; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">$45.60</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">Plan3<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G4</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(F4=TEXT(myDate,"mmm"),myAmt))}Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Workbook Defined Names<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>myAmt</TH><TD style="TEXT-ALIGN: left">=INDIRECT("D$4:D$"&(COUNTA(Plan3!$D$4:$D$1000)+ROWS(Plan3!$D$1:$D$3)))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>myDate</TH><TD style="TEXT-ALIGN: left">=INDIRECT("A$4:A$"&(COUNTA(Plan3!$A$4:$A$1000)+ROWS(Plan3!$A$1:$A$3)))

Markmzz
 
Last edited:
Upvote 0
No good, here's what I got.
Excel Workbook
ABCDEF
1Gas Costs
2Total YTD66.673$234.56
3DateGallonsPriceSaleMay#N/A
45/19/20118.249$3.759$31.01
55/26/201112.091$3.639$44.00
66/4/201111.037$3.459$38.18
76/18/201111.632$3.499$40.70
86/27/201110.288$3.409$35.07
97/8/201113.376$3.409$45.60
10
Gas
Excel 2010
Cell Formulas
RangeFormula
B2=SUM(B4:B9992)
D2=SUM(D4:D9999)
D4=IF(C4="","",C4*B4)
D5=IF(C5="","",C5*B5)
D6=IF(C6="","",C6*B6)
D7=IF(C7="","",C7*B7)
D8=IF(C8="","",C8*B8)
D9=IF(C9="","",C9*B9)
D10=IF(C10="","",C10*B10)
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Excel Workbook
NameRefers To
myAmt=INDIRECT("D$4:D$"&(COUNTA(Gas!$D$4:$D$1000)+ROWS(Gas!$D$1:$D$3)))
MyDate=INDIRECT("A$4:A$"&(COUNTA(Gas!$A$4:$A$1000)+ROWS(Gas!$A$1:$A$3)))
Workbook Defined Names
 
Upvote 0
I'm sorry. I didn't see that your data layout is not the same of mine.

Could you post an example of your worksheet?

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Calculating a YTD Balance based on end date?
Replies
2
Views
109
des378
D
R
  • Solved
Is it possible for an Excel formula to calculate a sum based on date ranges?
Replies
4
Views
116
Joe4
Joe4
O
  • Solved
Dynamic (row) selection in index formula based on date selection
2
Replies
10
Views
529
DanteAmor
DanteAmor
M
  • Question Question
Divide one number into multiple cells based on empty status
Replies
1
Views
66
Fluff
Fluff
L
  • Question Question
Sum of a dynamic number of rows
2
Replies
10
Views
551
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,583
Messages
6,179,681
Members
452,937
Latest member
Bhg1984

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