Summing a v lookup

[cjohnsen]

how can i detect a date range in a column then sum the values in another column? thanks in advance for the help.



the table looks like
11/24/06 100
11/24/06 200
11/24/06 3000
11/24/06
11/24/06
11/25/06 100

how can i get a sum for the 24th to give me 3300 ???????

[starryknight64]

Here ya go! Just copy and paste.

NOTE: This assumes that the dates are located in column A, starting at row 1. If the dates are located elsewhere, simply change the col and row variables to reflect that.

Please Login or Register  to view this content.

Enjoy!

[Mallycat]

how can i get a sum for the 24th to give me 3300 ???????

Another way is this.

Assuming your dates are in A1:A10 and your values are in B1:B10
Then use the following as an array formula.

=SUM(IF(A1:A10=DATE(2006,1,1),B1:B10,0))

Copy the formula into a cell, then press Ctrl+Shft+Enter. This will create an array formula and Excel will automatically add {} braces around the formula. If you just press enter, it wont work. For a good read about array formulas, have a look at this site http://www.cpearson.com/excel/array.htm

[cjohnsen]

this is great thank you so much for your help. i was able to get the formula to work the only thing is this sums the entire column B if the one date is correct. i would only need this to sum the values that correspond to that date.... any more suggestions would be greatly appreciated!!



=SUM(IF(A1:A10=DATE(2006,1,1),B1:B10,0))



how can i attach a file for further help?


i need to have a by day list of dates
8/24/06 5100
8/25/06 3000
8/26/06
8/27/06
8/28/06

that pulls from a bigger list of dates
8/24/06 100
8/24/06 5000
8/25/06 1000
8/25/06 2000
ect...
and sums the values in a different column but the same row.......

[starryknight64]

Try my code, it does exactly as you specified. Just plug in starting row and column and you're golden.

However, to fix up =SUM(IF(A1:A10=DATE(2006,1,1),B1:B10,0)), simply change the date to what you need, as well as the A1:A10. But the range summed (B1:B10)will need to be fixed to the range of like dates. You'll have to do this for each date.

That, unfortunately, is the downside to excel programming (non-macro). You don't always get exactly what you want.

[fankairong]

=SUM(IF(DAY(A1:A6)=24,B1:B6,0)),Then Ctrl+Alt+Enter

[cjohnsen]

this is great thank you!

=SUM(IF(A1:A10=DATE(2006,1,1),B1:B10,0)),

senerio:
i upload a sheet from a vendor daily and need to not change anything but the date for each formula. the thing is, i get mostly the same data each day but occasionally i there will be an extra entry for weeks past and literally that messes up my entire sheet. i find myself going back and changing cell references daily and this is a very time consuming task.....

if i could have each date search through the column of multiple and redundant dates for its corresponding date then total the value in the next column that would save me!!!

how can i input your code ??? where does that fit in?


thank you so much for your help this site is great!