If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
countifs - what is instead in excel 2000???
Hi
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 |
#2
|
|||
|
|||
countifs - what is instead in excel 2000???
Hi Amir
Try =SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*(AP5 :AP29=39601)*(ISNUMBER(FIND("box",AS5:AS29)))) -- Regards Roger Govier "amir2000" wrote in message ... Hi 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 |
#3
|
|||
|
|||
countifs - what is instead in excel 2000???
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 |
#4
|
|||
|
|||
countifs - what is instead in excel 2000???
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 |
#5
|
|||
|
|||
countifs - what is instead in excel 2000???
Let's meet half way, Amir. Why don't you tell us what formula you used. Then
we can provide solutions. Regards, Fred. "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 |
#6
|
|||
|
|||
countifs - what is instead in excel 2000???
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 |
#7
|
|||
|
|||
countifs - what is instead in excel 2000???
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 |
#8
|
|||
|
|||
countifs - what is instead in excel 2000???
It should be
=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*(AP5 :AP29=39601)*(ISNUMBER(FIND("box*",AS5:AS29)))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "amir2000" wrote in message ... 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 |
#9
|
|||
|
|||
countifs - what is instead in excel 2000???
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 |
#10
|
|||
|
|||
countifs - what is instead in excel 2000???
Note: the formula I suggested will only count rows where all columns, AL, AN
and AP contain the date AND AS begins with "box". Your multiple COUNTIFS formula counts the date columns separately so to replicate that exactly you'd need a small change, i.e. =SUMPRODUCT((AL5:AL29=39601)+(AN5:AN29=39601)+(AP5 :AP29=39601),--(LEFT(AS5:AS29,3)="box")) "daddylonglegs" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|