| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| translating countifs function to Excel 2003 | Excel Worksheet Functions | |||
| Countifs Fx in 07 how in 03? | Excel Discussion (Misc queries) | |||
| Excel CountIfs() and SumIfs() question | Excel Worksheet Functions | |||
| countifs | Excel Discussion (Misc queries) | |||
| 2 COUNTIFS | Excel Discussion (Misc queries) | |||
Hybrid Mode
