formulas calculating incorrectly

Hello all.

This is my first time utilizing this forum. First and foremost, I appreciate your time and input.

Here's my issue: I'm using an excel spreadsheet/report generated from one of my colleagues in another department to pull out selective data. However, the formulas are calculating incorrectly. They don't seem to recognize the data from the report. For example, in one portion I'm trying to determine how many instances of a scenario occur within a certain date range. A simple array formula that has worked for me a thousand times before isn't in this spreadsheet (example below). I've made sure that the dates are formatted as such and other details I'm aware of that would affect these calculations.

Can you assist me in trouble-shooting?

Below is an example. Please keep in mind that when I open a new spreadsheet enter in dates and try these formulas out, they work fine. I'm perplexed.

=SUMPRODUCT(--(D10:D183>=DATE(2009,8,9)),--(D10:D183<=DATE(2009,8,15)))

My objective here is to count those dates that fall into the given date range. I'm then going to add another criteria, but this is a start.

Thanks again!

We'll start off by asking if you have checked that Automatic calculation is on...

Tools|Options.. Calculation Tab... Automatic must be selected.

Does this work?

=SUMPRODUCT(--(D10:D183+0>=DATE(2009,8,9)),--(D10:D183+0<=DATE(2009,8,15)))

The +0 coerces the text string that looks like a number to be an actual number and so you can now compare to the dates (which are also numbers)...compare oranges to oranges... not apples to oranges...

To add another condition, just simply add the condition like so...

=SUMPRODUCT(--(D10:D183+0>=DATE(2009,8,9)),--(D10:D183+0<=DATE(2009,8,15)),--(E10:E183="Pre-procedure"))

where E10:E183 is the range to review for matching that string...

Originally Posted by Toidz77

I see now. I'm still wondering why manually changing the formats of the data to number didn't work.

you would have to change the format by selecting the column and going to Data|Text to Columns and clicking Finish... that should change the column to number format and your original formula should then work too...

Also, don't forget to mark your thread as [Solved] see FAQ's in my sig to know how.

Your last condition was offset by 2 rows down... maybe part of the problem

=SUMPRODUCT(--(G3:G3000+0>=DATE(2009,8,17)),--(G3:G3000+0<=DATE(2009,8,17))--(J3:J3000="Pre-procedure"))

Not sure what you are showing me here

Did you adjust the ranges and check your dates?

Your less than or equal to date is the same as your greater than or equal to.. so it only actually looks for the data Aug 17, 2009 for matches.. is that supposed to be?

I don't see any August dates in your spreadsheet...

another thing I noted about your formula is the absence of another comma separating the last argument:

Please Login or Register  to view this content.