Sum data depending on a date

awwcraig

New Member
Joined
Dec 1, 2009
Messages
37
I have a workbook full of a bunch of sheets with the same formatting. Each sheet has data for a 2 week period.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I am making a “master” type sheet and I’m hoping to be able to pull some data from each sheet.<o:p></o:p>
<o:p></o:p>
On each sheet:<o:p></o:p>
Row 1 has dates starting on E1 and ending on AF1 (several blank/combined columns)<o:p></o:p>
Row 22 has the data I want to pull, and it is one column to the right of the date. So, F22 corresponds with E1…<o:p></o:p>
<o:p></o:p>
Ex.<o:p></o:p>
One sheet looks like this:<o:p></o:p>
E1 = 8/24/09<o:p></o:p>
AF1 = 9/6/09<o:p></o:p>
<o:p></o:p>
I got single amounts to work by using:<o:p></o:p>
<o:p></o:p>
=IF((MONTH(E1)=8),F22," ")<o:p></o:p>
<o:p></o:p>
However, I’m hoping to find a formula that would look at the entire two week period and sum the row 22 amounts for each day that falls within a certain month.<o:p></o:p>
<o:p></o:p>
I tried:<o:p></o:p>
=IF((MONTH(E1:AG1)=8),F22:AG22," ")<o:p></o:p>
<o:p></o:p>
Which comes back false since not all days in the two week period fall within August (8).<o:p></o:p>
<o:p></o:p>
Does anyone have any ideas for a formula that would be easily changeable, and that would pull & sum row 22 data for each row 1 date that falls within a certain month?<o:p></o:p>
<o:p></o:p>
Thanks much,<o:p></o:p>
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, welcome to the board!

Try this:

=SUMPRODUCT(--(E1:AF1>=E1),--(E1:AF1<=AF1),F22:AG22)
 

awwcraig

New Member
Joined
Dec 1, 2009
Messages
37
Thanks for the quick response,

Using that forumla seems to result in the sum of all row 22 which has dates in August and September. I'm looking to get separate amounts for each month.

It seems like I'd need an IF statement somewhere to have the formula make sure the date in row 1 falls within the month I'm interested in.

I'm pretty new to the complex formulas though.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
So the ending date is only important if it is in the same month as the starting date?
 

awwcraig

New Member
Joined
Dec 1, 2009
Messages
37

ADVERTISEMENT

I'm not sure I follow.

The dates are just a reference, it is the data in row 22 that I am interested in gathering.

In some sheets all the dates fall within the same month, but in others there are dates in two different months.

I'm looking for a formula that will only pickup the row 22 data when the corresponding date in row 1 falls within a certain month.

This formula:
=IF((MONTH(E1)=8),F22," ")
Worked for individual dates, but I'd like a variation of it that I could apply to the two week range in each sheet.

Definitely been a pain trying to figure it out so far, I may just end up using a work-around.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
My formula only returns results that were between the two dates you specified. Is that not what you want? Or do you need two results because part of that time range is in a differerent month than you started in

With the example you gave, my formula will not sum values that correspond to dates before 8/24/09 or after 9/6/09 per your example.
 

awwcraig

New Member
Joined
Dec 1, 2009
Messages
37

ADVERTISEMENT

Ah, I see. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
The dates I listed are just the 2 week range of data within one of my worksheets<o:p></o:p>
<o:p></o:p>
E1 = 8/24/09
AF1 = 9/6/09<o:p></o:p>

<o:p></o:p>
Each sheet has 2 weeks worth of data and I'm making a master sheet to get monthly totals. (Which most times, will need to gather data from multiple sheets & exclude any dates falling in different months)<o:p></o:p>
<o:p></o:p>
So, I'm trying to get a formula that will sum all row 22 items that correspond to a date within a certain month. <o:p></o:p>
<o:p></o:p>
Right now, I’m just trying to get a formula to work in a regular sheet. Once I get that, then I’ll know how to tweak it on my “master” sheet to get the same data from every one of my other sheets.
<o:p></o:p>
The problem I'm running into is having a formula that applies to the 2 week range of dates returning the TRUE value rather than FALSE. The FALSE keeps showing up because the formulas I’ve tried are dependent on all dates in the range being in the same month. <o:p></o:p>
<o:p> </o:p>
In this case (Dates 8/24/09 – 9/6/09), every statement I can think of making returns FALSE/blank because of the dates 9/1/09-9/6/09. <o:p></o:p>
<o:p></o:p>
<o:p> </o:p>
I tried this and it would work if the 2 week range all fell within the same month, but for most of my sheets that isn’t the case.
<o:p> </o:p>
=IF(AND((MONTH(E1)=8),(MONTH(G1)=8),(MONTH(I1)=8),(MONTH(K1)=8),(MONTH(M1)=8),(MONTH(O1)=8),(MONTH(Q1)=8),(MONTH(T1)=8),(MONTH(V1)=8),(MONTH(X1)=8),(MONTH(Z1)=8),(MONTH(AB1)=8),(MONTH(AD1)=8),(MONTH(AF1)=8)),SUM(F22:AG22)," ")
<o:p> </o:p>
Maybe I need to make a table, but I don’t know if that’d screw up everything else I have.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
and you want to ignore the dates in September, because it's in a different month than the starting date?
Otherwise, if you want the whole date range, my formula does that.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
=SUMPRODUCT(--(E1:AF1>=E1),--(E1:AF1<=AF1),--(MONTH(E1:AF1)=MONTH(E1)),F22:AG22)

This will pull all the values based on the month in E1.

or if you want to specify a month number in a cell, say A1

=SUMPRODUCT(--(E1:AF1>=E1),--(E1:AF1<=AF1),--(MONTH(E1:AF1)=A1),F22:AG22)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,474
Messages
5,601,880
Members
414,479
Latest member
Beau the dog

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
Top