| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
How to get number of rows which match criteria
Coloum A Column B
Closed Settings Open Settings Closed Prametes Open UI I want to calculate Number of rows which have in Column A Closed and in Column B Settings . Please help .. |
|
#2
|
|||
|
|||
|
One way would be
=SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings")) Change ranges to suit -- Regards Roger Govier "kiranmani" wrote in message ... Coloum A Column B Closed Settings Open Settings Closed Prametes Open UI I want to calculate Number of rows which have in Column A Closed and in Column B Settings . Please help .. |
|
#3
|
|||
|
|||
|
typo
=SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings")) -- HTH Bob Phillips "Roger Govier" wrote in message ... One way would be =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings")) Change ranges to suit -- Regards Roger Govier "kiranmani" wrote in message ... Coloum A Column B Closed Settings Open Settings Closed Prametes Open UI I want to calculate Number of rows which have in Column A Closed and in Column B Settings . Please help .. |
|
#4
|
|||
|
|||
|
Hi Bob
Thanks for that. However, I am confused (nothing new there!!). Normally I use the "*" in the SUMPRODUCT formula and would normally have submitted =SUMPRODUCT(--(A2:A100="Closed")*(B2:B100="Settings")) which works fine. However, following a post from J.E. the other day where he suggested the use of the "," as a separator was marginally more efficient, I substituted it in this formula (without testing) and you quite rightly pointed out it doesn't work. Perhaps I need more explanation on the difference between the two methods. -- Regards Roger Govier "Bob Phillips" wrote in message ... typo =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings")) -- HTH Bob Phillips "Roger Govier" wrote in message ... One way would be =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings")) Change ranges to suit -- Regards Roger Govier "kiranmani" wrote in message ... Coloum A Column B Closed Settings Open Settings Closed Prametes Open UI I want to calculate Number of rows which have in Column A Closed and in Column B Settings . Please help .. |
|
#5
|
|||
|
|||
|
Hi Roger,
I cannot believe that * versus -- would make any noticeable difference on any spreadsheet.. It might be more efficient as the * will do the coercion in the same step as the product, but that might also make it less so. And with some complex formulae, it will be better to coerce to numeric before the product. But as I said I cannot believe it would be noticeable. BTW, if you do use the * operator, you don't need to coerce the first part =SUMPRODUCT((A2:A100="Closed")*(B2:B100="Settings" )) Perhaps this can help you http://xldynamic.com/source/xld.SUMPRODUCT.html -- HTH Bob Phillips "Roger Govier" wrote in message ... Hi Bob Thanks for that. However, I am confused (nothing new there!!). Normally I use the "*" in the SUMPRODUCT formula and would normally have submitted =SUMPRODUCT(--(A2:A100="Closed")*(B2:B100="Settings")) which works fine. However, following a post from J.E. the other day where he suggested the use of the "," as a separator was marginally more efficient, I substituted it in this formula (without testing) and you quite rightly pointed out it doesn't work. Perhaps I need more explanation on the difference between the two methods. -- Regards Roger Govier "Bob Phillips" wrote in message ... typo =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings")) -- HTH Bob Phillips "Roger Govier" wrote in message ... One way would be =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings")) Change ranges to suit -- Regards Roger Govier "kiranmani" wrote in message ... Coloum A Column B Closed Settings Open Settings Closed Prametes Open UI I want to calculate Number of rows which have in Column A Closed and in Column B Settings . Please help .. |
|
#6
|
|||
|
|||
|
Might be of interest:
http://www.mrexcel.com/board2/viewtopic.php?t=73205 Roger Govier wrote: Hi Bob Thanks for that. However, I am confused (nothing new there!!). Normally I use the "*" in the SUMPRODUCT formula and would normally have submitted =SUMPRODUCT(--(A2:A100="Closed")*(B2:B100="Settings")) which works fine. However, following a post from J.E. the other day where he suggested the use of the "," as a separator was marginally more efficient, I substituted it in this formula (without testing) and you quite rightly pointed out it doesn't work. Perhaps I need more explanation on the difference between the two methods. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
|
#7
|
|||
|
|||
|
Hi Bob & Aladin
Thank you for the references. They were both very useful and all is now clear. -- Regards Roger Govier "Bob Phillips" wrote in message ... Hi Roger, I cannot believe that * versus -- would make any noticeable difference on any spreadsheet.. It might be more efficient as the * will do the coercion in the same step as the product, but that might also make it less so. And with some complex formulae, it will be better to coerce to numeric before the product. But as I said I cannot believe it would be noticeable. BTW, if you do use the * operator, you don't need to coerce the first part =SUMPRODUCT((A2:A100="Closed")*(B2:B100="Settings" )) Perhaps this can help you http://xldynamic.com/source/xld.SUMPRODUCT.html -- HTH Bob Phillips "Roger Govier" wrote in message ... Hi Bob Thanks for that. However, I am confused (nothing new there!!). Normally I use the "*" in the SUMPRODUCT formula and would normally have submitted =SUMPRODUCT(--(A2:A100="Closed")*(B2:B100="Settings")) which works fine. However, following a post from J.E. the other day where he suggested the use of the "," as a separator was marginally more efficient, I substituted it in this formula (without testing) and you quite rightly pointed out it doesn't work. Perhaps I need more explanation on the difference between the two methods. -- Regards Roger Govier "Bob Phillips" wrote in message ... typo =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings")) -- HTH Bob Phillips "Roger Govier" wrote in message ... One way would be =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings")) Change ranges to suit -- Regards Roger Govier "kiranmani" wrote in message ... Coloum A Column B Closed Settings Open Settings Closed Prametes Open UI I want to calculate Number of rows which have in Column A Closed and in Column B Settings . Please help .. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select rows of data in a worksheet on one criteria in multiple co | Excel Worksheet Functions | |||
| Copy Rows if Lookup Criteria Match | Excel Discussion (Misc queries) | |||
| Is there a limit to the number of rows that can be filtered | Excel Worksheet Functions | |||
| Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
| increasing the number of rows of a worksheet beyond 65536? | Excel Discussion (Misc queries) | |||
Linear Mode
