Hi again
i try it for a while and I found that it's not calculating the way i needed.
I need it to look in 3 columns of dates and if the date is correct, than I
need it to count the word box.
the word box is changing;
box 1
box 2
box 3
etc...
The formula that you guys suggested is calculating the date.
Is anyone can help?
it's very urgent.
Many thanks
Amir
--
www.amir2000.nl
"amir2000" wrote:
Hi
Now it's working perfect.
Thanks a lot for the help :-)
Amir
--
www.amir2000.nl
"Roger Govier" wrote:
hi
Thanks, I had meant to delete the asterisk when copying the OP's code but
forgot.
--
Regards
Roger Govier
"daddylonglegs" wrote in message
...
FIND function doesn't accept wildcards, if you try to find "box*" then
this
will look for "box" followed by a literal asterisk. Use the syntax that
Roger suggested, i.e. without the asterisk, that will work for "box"
contained amongst other text.
As you say that "box" would be the first 3 characters you could search for
that specifically with
=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*(AP5 :AP29=39601)*(LEFT(AS5:AS29,3)="box"))
"amir2000" wrote:
Hi Again
Sorry, but it does not work:
=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*AP5: AP29=39601)*(ISNUMBER(FIND("box*",AS5:AS29))
that's how i wrote it and the result is:
-
it's like it cant find the data.
Could you explain me again please?
Thanks,
Amir
--
www.amir2000.nl
"Bob Phillips" wrote:
Roger's should work for you if the columns are correct are there are
those
dates.
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"amir2000" wrote in message
...
Hi Again,
Thanks but it does not work.
a b c
1 19/05/08 20/05/08 box 1
2 19/05/08 20/05/08 box 1
3 19/05/08 20/05/08 box 2
4 20/05/08 21/05/08 box 2
5 20/05/08 21/05/08 box 3
I tried the solutions that you gave me but it stays empty.
Thanks,
Amir
---
www.amir2000.nl
"Max" wrote:
Think one option is Sumproduct, in this manner
Eg in D1: =sumproduct((A1:A10=111)*(B1:B10="xxx"))
will return the count of rows ("countifs") where
A1:A10 contains the number: 111,
and
B1:B10 contains the text: "xxx"
Adapt /add-on to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"amir2000" wrote:
I used in office2007 the function countifs, in office2000 it's not
working.
What can i use instead?
Example:
=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)
- I need to count the date from 3 columns and if its true then to
count
the
word box(number coming after).
Anyone can help??
Thanks,
Amir
--
www.amir2000.nl