Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 14th 05, 04:06 PM posted to microsoft.public.excel.worksheet.functions
Frick
 
Posts: n/a
Default Need Help with a Date Sum Product


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.


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4124 |
+-------------------------------------------------------------------+

--
Frick
------------------------------------------------------------------------
Frick's Profile: http://www.excelforum.com/member.php...o&userid=29629
View this thread: http://www.excelforum.com/showthread...hreadid=493413


  #2   Report Post  
Old December 14th 05, 07:38 PM posted to microsoft.public.excel.worksheet.functions
wjohnson
 
Posts: n/a
Default Need Help with a Date Sum Product


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",D1326)

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

  #3   Report Post  
Old December 14th 05, 08:57 PM posted to microsoft.public.excel.worksheet.functions
Frick
 
Posts: n/a
Default Need Help with a Date Sum Product


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?


--
Frick
------------------------------------------------------------------------
Frick's Profile: http://www.excelforum.com/member.php...o&userid=29629
View this thread: http://www.excelforum.com/showthread...hreadid=493413

  #4   Report Post  
Old December 15th 05, 02:14 AM posted to microsoft.public.excel.worksheet.functions
wjohnson
 
Posts: n/a
Default Need Help with a Date Sum Product


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"


+-------------------------------------------------------------------+
|Filename: Example1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4127 |
+-------------------------------------------------------------------+

--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=493413



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a schedule from a list of dates ? Gavin Morris Charts and Charting in Excel 2 October 28th 10 04:08 PM
Another Date issue. TimM Excel Worksheet Functions 1 November 17th 05 02:58 AM
NETWORKDAYS - Multiple Date Selection Annabelle Excel Discussion (Misc queries) 3 October 4th 05 07:04 PM
Date Math Problem Dkline Excel Worksheet Functions 4 March 4th 05 05:11 PM
date and time ladimples247 New Users to Excel 2 February 16th 05 09:52 PM


All times are GMT +1. The time now is 12:37 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017