Solved

excel formula

Posted on 2011-03-06
12
481 Views
Last Modified: 2012-05-11

Please can you help; I need to amend the formula held in sheet2 of my workbook ("Stats table") cell reference F6.

The formula current counts B14:B2000 sheet1

I need to amend this formula so that it only counts within a particular date range. The date range will be From/To F3:F4

So only dates which fall between “from” and “to” would be returned in the total count.

For example B17 sheet 1 would be omitted from the total count because the 07/03/2011 falls outside the date range in F3:F4 Sheet 2



Sun1.xls
0
Comment
Question by:Gazza83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 167 total points
ID: 35048396
Try this formula there

=SUMPRODUCT(('Low Volume'!B14:B20000>='Stats Table'!D3)*('Low Volume'!B14:B200002<='Stats Table'!D4))

Sid
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35048399
Hi there,

Perhaps you could use something like this ...

=SUMPRODUCT((('Low Volume'!$B$14:$B$2000)>=F3)*(('Low Volume'!$B$14:$B$2000)<=F4))

Open in new window


Zack
0
 

Author Comment

by:Gazza83
ID: 35048792
Thanks Guys,

This formula comes back with a  result of 1, when I know the result should equal 3:-

=SUMPRODUCT((('Low Volume'!$B$14:$B$2000)>=F3)*(('Low Volume'!$B$14:$B$2000)<=F4))

I changed the D3 to F3 and D4 to F3 but the result come back as #Name?

=SUMPRODUCT(('Low Volume'!B14:B20000>='Stats Table'!D3)*('Low Volume'!B14:B200002<='Stats Table'!D4))
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35048836
Maybe it's your formatting.  The actual value of F4 (Date To) is 3/6/2011, while it shows a format of Sun 06/03/2011.  I'm assuming you are looking at it wrong.  ??  Your format of F3:F4 is "ddd dd/mm/yyyy", while it should be "ddd mm/dd/yyyy" (without quotes of course).

Zack
0
 
LVL 14

Assisted Solution

by:Zack Barresse
Zack Barresse earned 166 total points
ID: 35048874
And also, FWIW, if you wanted a dynamic range (instead of hard coding your range) you could amend the formula to be...

=SUMPRODUCT((('Low Volume'!$B$14:INDEX('Low Volume'!$B:$B,MATCH(9.9E+307,'Low Volume'!$B:$B),1))>=F3)*(('Low Volume'!$B$14:INDEX('Low Volume'!$B:$B,MATCH(9.9E+307,'Low Volume'!$B:$B),1))<=F4))

Zack
0
 

Author Comment

by:Gazza83
ID: 35048999
double checked the formats so that they are all *dd/mm/yyyy unfortunatly no joy
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35049059
Gazza it is working fine for me. What dates are you putting in and where?

Sid
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35049084
I only see one value which matches the date range, so I'm not sure where you're getting 3 from.  ???

You only have four values on your 'Low Volume' worksheet.  1/3/2011, 1/3/2011, 3/1/2011, 3/7/2011.  For the dates between the date range you specified in F3 and F4 of 'Stats Table' worksheet, 2/1/2011 and 3/6/2011, only one date matches between that range.  Can you explain how it should be 3?

Zack
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 167 total points
ID: 35049132
Hello Gazza,

2 of your dates (the first two in 'Low Volume'!B14:B15) are formatted as text [check by using this formula, =ISNUMBER(B14), you'll get FALSE when it should be TRUE for dates]. If you use this formula where the +0 converts those text-formatted dates to true dates.....

=SUMPRODUCT(('Low Volume'!B14:B20000+0>=F3)*('Low Volume'!B14:B20000+0<=F4))

....then you'll get a result of 3 as required

but I recommend you make those dates consistently dates. Where do they come from? If you select that column and use

Data > text to columns > Finish then that will also convert and you won't need the +0s in the formula.....

regards, barry
0
 

Author Comment

by:Gazza83
ID: 35049192
I've kept the spreadsheet the same, only the formulas result should equal "3" but maybe I'm doing something wrong.
help.JPG
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35049196
Zack,

If the workbook is opened with UK regional settings then (and Gazza is UK based, I assume) then it looks to me as if all the dates in B14:B17 are in March (the first 3 all display the same) so those 3 should all be counted, I believe, but as I say above the first 2 are text-formatted so if you open with US date settings that makes the first 2 look different from the third.....

barry
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35049358
Regional Settings aside, it shouldn't matter.  The function returns what it should.  (And I saw the text on the first sheet, but the formula coerced it, so I wasn't worried about it.)  On the 'Stats Table' worksheet, the format shows different than the value.  I'm thinking this is what is confusing the OP.

Zack
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question