| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif from an offset column
Hi,
I have the following data: Logins Trimmed agent 933,858 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1) 66,788 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.0) 55,989 Mozilla/5.0(WindowsUWindowsNT5.1en-USrv:1.8.0.6)Gecko/20060728Firefox/1.5.0.6 52,918 Mozilla/4.0(compatibleMSIE6.0AOL9.0WindowsNT5.1) 50,868 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1InfoPath.1) 43,339 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1Maxthon) I can get a count of all unique agent strings using, say, IE6 by using COUNTIF(B:B,"*MSIE6.0*"). However, I'd like to get total of all logins by IE6 browsers. That is, I'd like a sum of all values in column A, where the corresponding cell in column B contains "*MSIE6.0^" Is this possible without reverting to VBA? Thanks Tim |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif from an offset column
|
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif from an offset column
Hi!
Try this: =SUMIF(B2:B7,"*MSIE6.0*",A2:A7) Biff wrote in message ups.com... Hi, I have the following data: Logins Trimmed agent 933,858 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1) 66,788 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.0) 55,989 Mozilla/5.0(WindowsUWindowsNT5.1en-USrv:1.8.0.6)Gecko/20060728Firefox/1.5.0.6 52,918 Mozilla/4.0(compatibleMSIE6.0AOL9.0WindowsNT5.1) 50,868 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1InfoPath.1) 43,339 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1Maxthon) I can get a count of all unique agent strings using, say, IE6 by using COUNTIF(B:B,"*MSIE6.0*"). However, I'd like to get total of all logins by IE6 browsers. That is, I'd like a sum of all values in column A, where the corresponding cell in column B contains "*MSIE6.0^" Is this possible without reverting to VBA? Thanks Tim |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif from an offset column
Exactly what I'm looking for, thanks!
Biff wrote: Hi! Try this: =SUMIF(B2:B7,"*MSIE6.0*",A2:A7) Biff wrote in message ups.com... Hi, I have the following data: Logins Trimmed agent 933,858 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1) 66,788 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.0) 55,989 Mozilla/5.0(WindowsUWindowsNT5.1en-USrv:1.8.0.6)Gecko/20060728Firefox/1.5.0.6 52,918 Mozilla/4.0(compatibleMSIE6.0AOL9.0WindowsNT5.1) 50,868 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1InfoPath.1) 43,339 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1Maxthon) I can get a count of all unique agent strings using, say, IE6 by using COUNTIF(B:B,"*MSIE6.0*"). However, I'd like to get total of all logins by IE6 browsers. That is, I'd like a sum of all values in column A, where the corresponding cell in column B contains "*MSIE6.0^" Is this possible without reverting to VBA? Thanks Tim |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif from an offset column
You're welcome. Thanks for the feedback!
Biff wrote in message ups.com... Exactly what I'm looking for, thanks! Biff wrote: Hi! Try this: =SUMIF(B2:B7,"*MSIE6.0*",A2:A7) Biff wrote in message ups.com... Hi, I have the following data: Logins Trimmed agent 933,858 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1) 66,788 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.0) 55,989 Mozilla/5.0(WindowsUWindowsNT5.1en-USrv:1.8.0.6)Gecko/20060728Firefox/1.5.0.6 52,918 Mozilla/4.0(compatibleMSIE6.0AOL9.0WindowsNT5.1) 50,868 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1InfoPath.1) 43,339 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1Maxthon) I can get a count of all unique agent strings using, say, IE6 by using COUNTIF(B:B,"*MSIE6.0*"). However, I'd like to get total of all logins by IE6 browsers. That is, I'd like a sum of all values in column A, where the corresponding cell in column B contains "*MSIE6.0^" Is this possible without reverting to VBA? Thanks Tim |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif from an offset column
If I understand the question correctly, you can just make a simple change to
the formula you already have. Just change it to a "sumif" by entering the following formula: =SUMIF(B:B,"*MSIE6.0*",A:A). Hope this helps. MM " wrote: Hi, I have the following data: Logins Trimmed agent 933,858 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1) 66,788 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.0) 55,989 Mozilla/5.0(WindowsUWindowsNT5.1en-USrv:1.8.0.6)Gecko/20060728Firefox/1.5.0.6 52,918 Mozilla/4.0(compatibleMSIE6.0AOL9.0WindowsNT5.1) 50,868 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1InfoPath.1) 43,339 Mozilla/4.0(compatibleMSIE6.0WindowsNT5.1Maxthon) I can get a count of all unique agent strings using, say, IE6 by using COUNTIF(B:B,"*MSIE6.0*"). However, I'd like to get total of all logins by IE6 browsers. That is, I'd like a sum of all values in column A, where the corresponding cell in column B contains "*MSIE6.0^" Is this possible without reverting to VBA? Thanks Tim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro question | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| How do I Excel countif column a =? and column b = null ? | Excel Worksheet Functions | |||
| IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
| creating a bar graph | Excel Discussion (Misc queries) | |||
Linear Mode
