| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Formula
Good Afternoon ...
This questions is probably redundant, however, my question is how to write the formula to count, and excluding certain criteria. For example: Column A 1111 2921 2222 2921 3344 3333 4444 Count the entries, but do not include the numbers 2921 and 3344. Results should equal 4. -- Thank you ... ceej |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Formula
=sumproduct(--(a1:a7<2921)*(--(a1:a7<3344))) -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=562254 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Formula
ceej Wrote: Good Afternoon ... This questions is probably redundant, however, my question is how to write the formula to count, and excluding certain criteria. For example: Column A 1111 2921 2222 2921 3344 3333 4444 Count the entries, but do not include the numbers 2921 and 3344. Results should equal 4. -- Thank you ... ceej One possibility is: =COUNT(A1:A7)-COUNTIF(A1:A7,"2921")-COUNTIF(A1:A7,"3344") Scott -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=562254 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Formula
FYI ... my data has 89 rows x 9 columns, with the criteria being in column G.
I tried the formula and the results were the exact number of rows, 89. I double-checked the data, and it is numberic. Any other suggestions?? -- Thank you ... ceej "VBA Noob" wrote: =sumproduct(--(a1:a7<2921)*(--(a1:a7<3344))) -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=562254 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Formula
So your criteria is in the middle of your table which is odd?? If you put your criteria in J1 and J2 then this should work =SUMPRODUCT(--(A1:I89<J1)*(--(A1:I89<J2))) VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=562254 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Formula
=SUMPRODUCT(--(A1:I89<J1),--(A1:I89<J2))
or =SUMPRODUCT((A1:I89<J1)*(A1:I89<J2)) no need for both -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "VBA Noob" wrote in message ... So your criteria is in the middle of your table which is odd?? If you put your criteria in J1 and J2 then this should work =SUMPRODUCT(--(A1:I89<J1)*(--(A1:I89<J2))) VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=562254 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Formula
=COUNT(IF(A1:I89<{2921,23344},A1:I89))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ceej" wrote in message ... FYI ... my data has 89 rows x 9 columns, with the criteria being in column G. I tried the formula and the results were the exact number of rows, 89. I double-checked the data, and it is numberic. Any other suggestions?? -- Thank you ... ceej "VBA Noob" wrote: =sumproduct(--(a1:a7<2921)*(--(a1:a7<3344))) -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=562254 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using the $ in a count formula | Excel Discussion (Misc queries) | |||
| Creating a Count formula | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| More Help Needed with Count formula | Excel Worksheet Functions | |||
| Count numbers formed from another formula | Excel Discussion (Misc queries) | |||
Linear Mode
