Sum formula help

[erniepoe]

Hi all,

So I'm trying to add the values of columns N and U when both columns O and V have a date value from July, 2011.


I have the following CSE formula, but it's still resulting in the #Value error. I think that I'm close, but if anyone could nudge me in the correct direction, I'd be most appreciative.



=SUM((O1:O81>=7/1/11)*(O1:O81<7/31/11)*(V1:V81>=7/1/11)*(V1:V81<7/31/11)*(N1:N81)*(U1:U81))

 

[wigi]

As a tip, store the 2 dates in 2 separate cells say A1 and A2. Then you have no issues with dates in your formula (that's where it breaks down).

=SUM((O1:O81>=$A$1)*(O1:O81<$B$1)*(V1:V81>=$A$1)*(V1:V81<$B$1)*(N1:N81)*(U1:U81))

 

[Jonmo1]

In addition to wigi's comments...

The reason it fails the way you have the dates is because...

(O1:O81>=7/1/11)
This is being read like
Is the value in Column O Greater than or equal to 7 devided by 1 devided by 11

To make it work you could do a few things..
1. Put the date in a cell as wigi suggests
2. (O1:O81>="7/1/11"+0)
3. (O1:O81>=DATE(2011,7,1))

Hope that helps.

 

[wigi]

In addition to wigi's comments...

The reason it fails the way you have the dates is because...

(O1:O81>=7/1/11)
This is being read like
Is the value in Column O Greater than or equal to 7 devided by 1 devided by 11

To make it work you could do a few things..
1. Put the date in a cell as wigi suggests
2. (O1:O81>="7/1/11"+0)
3. (O1:O81>=DATE(2011,7,1))

Hope that helps.

True Jonmo. The DATEVALUE function is another candidate here.

 

[erniepoe]

Thanks Jon,

So I now have


=SUM((O1:O81>=DATE(2011,7,1))*(O1:O81<DATE(2011,7,31))*(V1:V81>=DATE(2011,7,1))*(V1:V81<DATE(2011,7,31))*(N1:N81)*(U1:U81))


entered as a CSE, but still getting the #Value error. As it turns out, I want it to sum when EITHER column is in July 2011, not both as I accidentally stated before. Does this change the formula at all?

 

[erniepoe]

whoops, formula was


=SUM((O1:O81>=DATE(2011,7,1))*(O1:O81<DATE(2011,7,31))*(V1:V81>=DATE(2011,7,1))*(V1:V81<DATE(2011,7,31))*(N1:N81)*(U1:U81))

 

[erniepoe]

=SUM((O1:O81>=DATE(2011,7,1))*(O1:O81<DATE(2011,7,31))*(V1:V81>=DATE(2011,7,1))*(V1:V81<DATE(2011,7,31))*(N1:N81)*(U1:U81))


trying this again....

 

[erniepoe]

=SUM((O1:O81>=DATE(2011,7,1))*(O1:O81<DATE(2011,7,31))*(V1:V81>=DATE(2011,7,1))*(V1:V81<DATE(2011,7,31))*(N1:N81)*(U1:U81))


Why is this not showing up?

 

[VoG]

Surround < with spaces.

If X < 1

 

[erniepoe]

I give up. It pastes into this box the way I want, but then shows up as only a partial formula. What the.....?


Today is not my day.




SUM((O1:O81>= DATE (2011,7,1))*(O1:O81 < DATE(2011,7,31))*(V1:V81 >= DATE (2011,7,1))*(V1:V81< DATE (2011,7,31))*(N1:N81)*(U1:U81))


Edit: ok there we go