| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIFS alternative for older excel versions
The below formula works fine in excel 2007 but not 2003. Has anybody any idea
how I can get this to work in excel 2003. 1st Formula =COUNTIFS(F4:F66,"=5*",H4:H66,"=3") 2nd Formula =COUNTIFS(F5:F67,"=6*",H5:H67,"=3") f h target current 5a 1 5a 1 5a 2 5a 3 1st Formula 32 5a 4 2nd Formula 21 5a 4 5a 3 5a 1 5a 2 5a 2 5a 2 5a 2 5a 2 5b 1 5b 2 5b 3 5b 4 5b 4 5b 2 5b 4 5b 2 5b 2 5b 1 5c 1 5c 2 5c 3 5c 3 5c 3 6a 3 6a 3 6a 3 6a 3 6a 3 6a 3 6a 2 6a 3 6a 1 6b 4 6b 1 6b 2 6b 2 6b 3 6b 3 6b 3 6c 3 6c 2 6c 3 6c 2 6c 4 7a 3 7a 2 7b 1 7b 1 7b 4 7c 4 7c 2 7c 3 8a 4 8a 1 8b 2 8b 3 8c 1 8c 3 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIFS alternative for older excel versions
Maybe this
=COUNT(IF(SEARCH("5?",A2:A64),B2:B64)) 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correct then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Jon Mac" wrote: The below formula works fine in excel 2007 but not 2003. Has anybody any idea how I can get this to work in excel 2003. 1st Formula =COUNTIFS(F4:F66,"=5*",H4:H66,"=3") 2nd Formula =COUNTIFS(F5:F67,"=6*",H5:H67,"=3") f h target current 5a 1 5a 1 5a 2 5a 3 1st Formula 32 5a 4 2nd Formula 21 5a 4 5a 3 5a 1 5a 2 5a 2 5a 2 5a 2 5a 2 5b 1 5b 2 5b 3 5b 4 5b 4 5b 2 5b 4 5b 2 5b 2 5b 1 5c 1 5c 2 5c 3 5c 3 5c 3 6a 3 6a 3 6a 3 6a 3 6a 3 6a 3 6a 2 6a 3 6a 1 6b 4 6b 1 6b 2 6b 2 6b 3 6b 3 6b 3 6c 3 6c 2 6c 3 6c 2 6c 4 7a 3 7a 2 7b 1 7b 1 7b 4 7c 4 7c 2 7c 3 8a 4 8a 1 8b 2 8b 3 8c 1 8c 3 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIFS alternative for older excel versions
Hi,
The answer depend on whether 5* represents numbers or text if numbers then =SUMPRODUCT(--(F4:F66=50),--(H4:H66=3)) if text such as 5qwa then: =SUMPRODUCT(--(LEFT(B4:B66)="5"),--(C4:C66=3)) If this helps, click the Yes button Cheers, Shane Devenshire "Jon Mac" wrote: The below formula works fine in excel 2007 but not 2003. Has anybody any idea how I can get this to work in excel 2003. 1st Formula =COUNTIFS(F4:F66,"=5*",H4:H66,"=3") 2nd Formula =COUNTIFS(F5:F67,"=6*",H5:H67,"=3") f h target current 5a 1 5a 1 5a 2 5a 3 1st Formula 32 5a 4 2nd Formula 21 5a 4 5a 3 5a 1 5a 2 5a 2 5a 2 5a 2 5a 2 5b 1 5b 2 5b 3 5b 4 5b 4 5b 2 5b 4 5b 2 5b 2 5b 1 5c 1 5c 2 5c 3 5c 3 5c 3 6a 3 6a 3 6a 3 6a 3 6a 3 6a 3 6a 2 6a 3 6a 1 6b 4 6b 1 6b 2 6b 2 6b 3 6b 3 6b 3 6c 3 6c 2 6c 3 6c 2 6c 4 7a 3 7a 2 7b 1 7b 1 7b 4 7c 4 7c 2 7c 3 8a 4 8a 1 8b 2 8b 3 8c 1 8c 3 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Older Versions of Excel | Excel Discussion (Misc queries) | |||
| Alternative for =countifs (in 2007) for Excel 2003 | Excel Discussion (Misc queries) | |||
| Transferring formulas from older excel versions | Excel Worksheet Functions | |||
| Converting Excel 07 to older versions | Excel Discussion (Misc queries) | |||
| Older versions of Excel | Excel Discussion (Misc queries) | |||
Linear Mode
