About Us

Fred Smith[_4_]

Glad to help. Thanks for the feedback.

Fred

"hoyos" wrote in message
...
Yes your right, it was the date I had not defined it properly. its working
well with your formula......thank very much

"Fred Smith" wrote:

Sounds to me like one of your entries is not a date. It's text
masquerading
as a date. That's the first thing I'd check for.

If the formula works in some situations, but not others, then it's the
data,
not the formula.

Regards,
Fred

"hoyos" wrote in message
...
Another point. As I drag the formula down the column, all the rest are
correct. Just the first one is one down?

"hoyos" wrote:

Thanks Fred. I tried the formula but it does not count all the
entries.
Always gives results one less.
Any ideas?

"Fred Smith" wrote:

The one thing I can see is you need to turn the true/false of
Station=A2
into a number. Try:
=SUMPRODUCT(--(Date1=Orders!H3),--(Date1<=Orders!I3),--(Station=A2))

See what that does.

Regards,
Fred

"hoyos" wrote in message
...
I have tried to modify your code with no joy!
The code below is your code modified to suit the file, but its not
working
=SUMPRODUCT(--(Date1=Orders!H3),--(Date1<=Orders!I3),Station=A2)

Date1= difined (that is the column with dates)
Station= defined column "Z"
Orders!H3 and I3= start and finish dates

My original formula is
=SUMPRODUCT((Orders!$B$5:$B$64988=Orders!$H$3)*(O rders!$B$5:$B$64988<=Orders!$I$3)*(Orders!$Z$5:$Z$ 64988=A3))

I was after simplifying the formula by creating some dynamic
ranges
to use
in my formula.
I hope its a little clearer what I am tryinh to achiev.

"Fred Smith" wrote:

Something like:
=IF(AF16="",NA(),SUMPRODUCT(--(AF16=Date1),--(AF16<=Date2),Value)

Regards,
Fred

"hoyos" wrote in message
...
How can modify the formula below so that its criteria is
between
two
dates?

=IF(AF16="",NA(),SUMPRODUCT(--(Date=AF16),Value)

Thank you.

.

.

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
collect data from mail merge	data collection	Excel Worksheet Functions	1	November 24th 09 02:19 PM
collect data from one excel sheet to another	Sarcalogus	Excel Discussion (Misc queries)	3	October 15th 09 05:17 PM
Is there a way to use the P.O. template and collect/store data.	Leebob	Excel Discussion (Misc queries)	0	April 13th 07 01:46 PM
How to collect data from every 60th row?	Jim Ryan	Excel Discussion (Misc queries)	2	April 4th 06 05:28 AM
collect data from different worksheet	sheva	Excel Worksheet Functions	0	August 16th 05 03:22 PM

Menu

About Us