| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 version of Countifs
I have tried the suggested Excel 2003 solution to the Countifs function but
am getting stuck. I want to count the number of tems in a spreadsheet where two predefined conditions are met so where the month = x and where the status is "1" My original formula is: =_xlfn.COUNTIFS('HB Appeals'!$E$2:$E$65536,"=5",'HB Appeals'!$L$2:$L$65536,"=1") as I had it sorted on Excel 2007. I have tried: =SUMPRODUCT("--('HB Appeals'!e2:E200,4)","--('HB Appeals'!L2:L200,1)") as suggested but I am getting nowhere. Please adise |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 version of Countifs
=SUMPRODUCT("--('HB Appeals'!e2:E200,4)","--('HB Appeals'!L2:L200,1)")
You were pretty close. Try it like this: =SUMPRODUCT(--('HB Appeals'!E2:E200=4),--('HB Appeals'!L2:L200=1)) -- Biff Microsoft Excel MVP "Mr Swift" wrote in message ... I have tried the suggested Excel 2003 solution to the Countifs function but am getting stuck. I want to count the number of tems in a spreadsheet where two predefined conditions are met so where the month = x and where the status is "1" My original formula is: =_xlfn.COUNTIFS('HB Appeals'!$E$2:$E$65536,"=5",'HB Appeals'!$L$2:$L$65536,"=1") as I had it sorted on Excel 2007. I have tried: =SUMPRODUCT("--('HB Appeals'!e2:E200,4)","--('HB Appeals'!L2:L200,1)") as suggested but I am getting nowhere. Please adise |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 version of Countifs
Thank you,
but this doesn't work for me I have included a snapshot of the data below... I want to count the incidents where the month number is 4 and the revised status (which is validated by a 1) is true. What am I doing wrong? Date Rec'd Month Revision or Appeal Reason Further Info Required Nature of Info Required Date of FI Request Revised 2.4.09 4 Revision Nil qualified - incomplete info y 1 2.4.09 4 Nil qualified - no rent liability 2.4.09 4 Backdating refusal 6.4.09 4 Appeal Non dep deduction/overpayment 7.4.09 4 Appeal Backdating refusal y 1 14.4.09 4 Backdating refusal 17.4.09 4 Backdating refusal 21.4.09 4 Termination of housing benefit 24.4.09 4 Disagrees with income used 29.4.09 4 Overpayment 5.5.09 5 Backdating refusal 8.5.09 5 Overpayment/ownership of prop 13.5.09 5 Appeal Overpayment 19.5.09 5 Non dep deduction/overpayment 20.5.09 5 Revision Nil qualified - incomplete info y 1 21.5.09 5 Termination of housing benefit 26.5.09 5 Backdating refusal 29.5.09 5 Termination of housing benefit 01.6.09 6 Termination of housing benefit 6.5.09 5 Backdating refusal 1.6.09 6 Backdating refusal 3.6.09 6 Backdating refusal "T. Valko" wrote: =SUMPRODUCT("--('HB Appeals'!e2:E200,4)","--('HB Appeals'!L2:L200,1)") You were pretty close. Try it like this: =SUMPRODUCT(--('HB Appeals'!E2:E200=4),--('HB Appeals'!L2:L200=1)) -- Biff Microsoft Excel MVP "Mr Swift" wrote in message ... I have tried the suggested Excel 2003 solution to the Countifs function but am getting stuck. I want to count the number of tems in a spreadsheet where two predefined conditions are met so where the month = x and where the status is "1" My original formula is: =_xlfn.COUNTIFS('HB Appeals'!$E$2:$E$65536,"=5",'HB Appeals'!$L$2:$L$65536,"=1") as I had it sorted on Excel 2007. I have tried: =SUMPRODUCT("--('HB Appeals'!e2:E200,4)","--('HB Appeals'!L2:L200,1)") as suggested but I am getting nowhere. Please adise |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 version of Countifs
but this doesn't work for me
When I copy/paste your data into Excel I get a result of 2. Explain what "doesn't work" means. -- Biff Microsoft Excel MVP "Mr Swift" wrote in message ... Thank you, but this doesn't work for me I have included a snapshot of the data below... I want to count the incidents where the month number is 4 and the revised status (which is validated by a 1) is true. What am I doing wrong? Date Rec'd Month Revision or Appeal Reason Further Info Required Nature of Info Required Date of FI Request Revised 2.4.09 4 Revision Nil qualified - incomplete info y 1 2.4.09 4 Nil qualified - no rent liability 2.4.09 4 Backdating refusal 6.4.09 4 Appeal Non dep deduction/overpayment 7.4.09 4 Appeal Backdating refusal y 1 14.4.09 4 Backdating refusal 17.4.09 4 Backdating refusal 21.4.09 4 Termination of housing benefit 24.4.09 4 Disagrees with income used 29.4.09 4 Overpayment 5.5.09 5 Backdating refusal 8.5.09 5 Overpayment/ownership of prop 13.5.09 5 Appeal Overpayment 19.5.09 5 Non dep deduction/overpayment 20.5.09 5 Revision Nil qualified - incomplete info y 1 21.5.09 5 Termination of housing benefit 26.5.09 5 Backdating refusal 29.5.09 5 Termination of housing benefit 01.6.09 6 Termination of housing benefit 6.5.09 5 Backdating refusal 1.6.09 6 Backdating refusal 3.6.09 6 Backdating refusal "T. Valko" wrote: =SUMPRODUCT("--('HB Appeals'!e2:E200,4)","--('HB Appeals'!L2:L200,1)") You were pretty close. Try it like this: =SUMPRODUCT(--('HB Appeals'!E2:E200=4),--('HB Appeals'!L2:L200=1)) -- Biff Microsoft Excel MVP "Mr Swift" wrote in message ... I have tried the suggested Excel 2003 solution to the Countifs function but am getting stuck. I want to count the number of tems in a spreadsheet where two predefined conditions are met so where the month = x and where the status is "1" My original formula is: =_xlfn.COUNTIFS('HB Appeals'!$E$2:$E$65536,"=5",'HB Appeals'!$L$2:$L$65536,"=1") as I had it sorted on Excel 2007. I have tried: =SUMPRODUCT("--('HB Appeals'!e2:E200,4)","--('HB Appeals'!L2:L200,1)") as suggested but I am getting nowhere. Please adise |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 version of Countifs
Double check your formula, the one that T. Valko gave you should work IF your
4's (month number) are in column B and the 1's are in column L. Somehow I suspect (based on your snapshot) that the 1's are not in column L?? "Mr Swift" wrote: Thank you, but this doesn't work for me I have included a snapshot of the data below... I want to count the incidents where the month number is 4 and the revised status (which is validated by a 1) is true. What am I doing wrong? Date Rec'd Month Revision or Appeal Reason Further Info Required Nature of Info Required Date of FI Request Revised 2.4.09 4 Revision Nil qualified - incomplete info y 1 2.4.09 4 Nil qualified - no rent liability 2.4.09 4 Backdating refusal 6.4.09 4 Appeal Non dep deduction/overpayment 7.4.09 4 Appeal Backdating refusal y 1 14.4.09 4 Backdating refusal 17.4.09 4 Backdating refusal 21.4.09 4 Termination of housing benefit 24.4.09 4 Disagrees with income used 29.4.09 4 Overpayment 5.5.09 5 Backdating refusal 8.5.09 5 Overpayment/ownership of prop 13.5.09 5 Appeal Overpayment 19.5.09 5 Non dep deduction/overpayment 20.5.09 5 Revision Nil qualified - incomplete info y 1 21.5.09 5 Termination of housing benefit 26.5.09 5 Backdating refusal 29.5.09 5 Termination of housing benefit 01.6.09 6 Termination of housing benefit 6.5.09 5 Backdating refusal 1.6.09 6 Backdating refusal 3.6.09 6 Backdating refusal "T. Valko" wrote: =SUMPRODUCT("--('HB Appeals'!e2:E200,4)","--('HB Appeals'!L2:L200,1)") You were pretty close. Try it like this: =SUMPRODUCT(--('HB Appeals'!E2:E200=4),--('HB Appeals'!L2:L200=1)) -- Biff Microsoft Excel MVP "Mr Swift" wrote in message ... I have tried the suggested Excel 2003 solution to the Countifs function but am getting stuck. I want to count the number of tems in a spreadsheet where two predefined conditions are met so where the month = x and where the status is "1" My original formula is: =_xlfn.COUNTIFS('HB Appeals'!$E$2:$E$65536,"=5",'HB Appeals'!$L$2:$L$65536,"=1") as I had it sorted on Excel 2007. I have tried: =SUMPRODUCT("--('HB Appeals'!e2:E200,4)","--('HB Appeals'!L2:L200,1)") as suggested but I am getting nowhere. Please adise |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| countifs on earlier version of excel | Excel Worksheet Functions | |||
| Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
| Alternative for =countifs (in 2007) for Excel 2003 | Excel Discussion (Misc queries) | |||
| translating countifs function to Excel 2003 | Excel Worksheet Functions | |||
| download trial version excel 2003? can only find trial version 200 | Excel Discussion (Misc queries) | |||
Linear Mode
