Sum data depending on a date

[awwcraig]

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-comfficeffice" /><o></o>
<o></o>
I am making a “master” type sheet and I’m hoping to be able to pull some data from each sheet.<o></o>
<o></o>
On each sheet:<o></o>
Row 1 has dates starting on E1 and ending on AF1 (several blank/combined columns)<o></o>
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></o>
<o></o>
Ex.<o></o>
One sheet looks like this:<o></o>
E1 = 8/24/09<o></o>
AF1 = 9/6/09<o></o>
<o></o>
I got single amounts to work by using:<o></o>
<o></o>
=IF((MONTH(E1)=8),F22," ")<o></o>
<o></o>
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></o>
<o></o>
I tried:<o></o>
=IF((MONTH(E1:AG1)=8),F22:AG22," ")<o></o>
<o></o>
Which comes back false since not all days in the two week period fall within August (8).<o></o>
<o></o>
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></o>
<o></o>
Thanks much,<o></o>

 

[Scott Huish]

Hi, welcome to the board!

Try this:

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

 

[awwcraig]

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]

So the ending date is only important if it is in the same month as the starting date?

 

[awwcraig]

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]

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]

Ah, I see. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The dates I listed are just the 2 week range of data within one of my worksheets<o></o>
<o></o>
E1 = 8/24/09
AF1 = 9/6/09<o></o>
<o></o>
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></o>
<o></o>
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></o>
<o></o>
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></o>
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></o>
<o> </o>
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></o>
<o></o>
<o> </o>
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> </o>
=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> </o>
Maybe I need to make a table, but I don’t know if that’d screw up everything else I have.

 

[Scott Huish]

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.

 

[awwcraig]

Ya I want it to ignore September

 

[Scott Huish]

=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)