Excel - I need a date formula 2

[eslayter]

Hi everyone...

I'm stumped on what is probably a pretty easy problem:

I want to put a formula in cell E5 that uses the range from E2&E3 to sum column B based on the dates in column A.

For example, the total would be 65 for the range shown (February).

I've tried using sumif, but the criteria keeps putting quotes in and that causes excel to not use the serial code for the date.

Thanks for any help.

-Erik

 

[xlhelp]

In cell E5 type

=SUM(IF((A2:A9>=E2)*(A2:A9<=E3),B2:B9,0))

and press CTRL/SHIFT/ENTER to make it a an array formula.

HTH

Indu

 

[eslayter]

Indu/xlhelp-

Thanks for the quick response and the great formula. If I may trouble you, can I get an explanation of how this formula works so I can learn from you on this.

Specifically, the * in the If statement is something I've never seen before. How does that work?

Secondly, I've never quite got the hang of arrays...any explanation you can give me on how the array works would be beneficial for me.

Again, thanks.

-Erik

 

[xlhelp]

Hi, Erik!

The star denotes the Boolean AND. So we are saying &quot;if this&quot; AND &quot;if that&quot;. The formula works only if both conditions are True and as such prouces a 1; if 1 or both conditions are false then it produces a zero.

If you didn't use the array formula the result would be 192 or zero. The reason is that it would take the very first test and if it's 1 then give you total of all cells and if it's zero then it will give you nothing.

When you tell it to use the array, it tests each condition in the array and count only those cells that qualify.

That's the Q&D explanation.

HTH

Indu

 

[xlbo]

Just a little addendum to Indu's largely accurate response.
I don't believe that * does denote the BOOLEAN AND in this case.
What an array formula does is build up an internal array
The test statements eg IF((A2:A9>=E2) return a boolean answer of true or false. Boolean values as numbers are TRUE = 1 and FALSE = 0. Therefore for your dataset, you get an internal array like:
0*0
0*0
1*1
1*1
1*1
0*0
0*0
etc etc
If one condition is true and the other false, you would get 1*0
If you were doing a count, you would stop there and the SUM of the results of the internal matrix is your count answer.
Because you need a sum of values, you then have
B2:B9 which contains the values so you get
0*0*10 = 0
0*0*13 = 0
1*1*18 = 18
1*1*22 = 22
1*1*25 = 25
0*0*21 = 0
0*0*31 = 0
SUM the results and you have your answer
HTH
Geoff

 

[zollo9999]

eslayter

I was impressed that you pasted the xl sheet into your post.
How did you do that?

thanks in advance
Zollo

 

[eslayter]

I made the example in XL. Then copied and pasted it into Paint. I had to manually add the column and row letters/numbers. Saved as a bmp or jpg (forget which now) and uploaded to a web server that I have access to. Used the html abilities of the forum to link to that pic on the website. I wasn't sure it would work, but thought it would be slick if it did...and make it much easier to get a good response.

-Erik