Need Help with a Date Sum Product

[Frick]

I have attached a small worksheet that explains my two problems.

My first problem. In one column I have various dates entered, in the next column are numbers. What I need to be able to do is based on a start and end date, I need to find all the data within the date range.

My second problem. I need to drop either 1,2, or 3 lowest scores either based on all the data in each column (this is easy formula and I've done that) or based on the date range.

Thanks for all help offered. I hope I have sufficiently explained my problems.

[wjohnson]

This may not be what you want but it will work
Insert 1 column to the right of your dates (Row 13 thru 25) and then
enter the following formula.
=IF(AND(B13>=$D$7,B13<=$F$7),$C$7,IF(AND(B13>=$D$8,B13<=$F$8),$C$8,IF(AND(B13>=$D$9,B13<=$F$9),$C$9,"Check"))).
The above formual will put a #1, #2, #3 into the column to indicate
semster.

Then in cell D41 do the following formula:
=SUMIF(C13:C26,"=#1",D13:D26)

The "straight" SUMIF probably won't work because you have an "AND"
condition

Also if it doesn't compute correctly you may have to use DD MMM YY
format


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.hightechtalks.com/m390
View this thread: http://www.hightechtalks.com/t2309223

[Frick]

wjohnson. Thanks for the help. The formulas work fine. Did you have a chance to look at the second problem and if so any ideas how to solve it?

[wjohnson]

I am able to make it work but 2 more columns were added
I added a "RANK" column to rank the values in Reverse order to rank the lowest number 1st.
2nd column is an 'AND" column to verify your 2 conditions -
condition 1 - "What Semester" and Condition 2 - "What Ranking" you do not want.

Then I did a "sumif" and everything seemed to work.

I have attached your spreadsheet with my additions highlighted in "yellow" and "bold"