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

Countif from an offset column
 

Take a look at =sumif()

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

--

Dave Peterson

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

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

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

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