[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

excel count if

Hi,

I need find an efficient way to search daily exchange log files to monitor the number of emails sent with a particular email subject.

In the attached excel model:

Sheet1 is the exchanges logs that could have 50,000 lines
Sheet2 is an example of what I would link to finish with

Sheet2  B2 =

Where
sheet1, column S =  Copy Invoice for Company A
and
sheet1, column T = A@exchange.co.uk
count
total number of es1100.mail.exchange.co.uk in sheet1, column E
In this example the total is 1
Sheet2  B3 =

Where
sheet1, column S =  Copy Invoice for Company B
and
sheet1, column T = B@exchange.co.uk
count
total number of es1100.mail.exchange.co.uk in sheet1, column E
In this example the total is 2
0
hellblazeruk
Asked:
hellblazeruk
1 Solution
 
hellblazerukAuthor Commented:
0
 
Glenn RayExcel VBA DeveloperCommented:
You can use the COUNTIFS function in Excel 2010/2013 to solve this.  For B2 (Company A), it would look like:
=COUNTIFS(Sheet1!S:S," Copy Invoice for Company A",Sheet1!T:T,"A@exchange.co.uk",Sheet1!E:E,"es1100.mail.exchange.co.uk")

and for B3 (Company B)
=COUNTIFS(Sheet1!S:S," Copy Invoice for Company B",Sheet1!T:T,"B@exchange.co.uk",Sheet1!E:E,"es1100.mail.exchange.co.uk")

However, it looks like you're using an earlier version of Excel, so you'll need to use a SUMPRODUCT function instead.

B2:
=SUMPRODUCT(--(Sheet1!$S$2:$S$100=" Copy Invoice for Company A"),--(Sheet1!$T$2:$T$100="A@exchange.co.uk"),--(Sheet1!$E$2:$E$100="es1100.mail.exchange.co.uk"))
B3:
=SUMPRODUCT(--(Sheet1!$S$2:$S$100=" Copy Invoice for Company B"),--(Sheet1!$T$2:$T$100="B@exchange.co.uk"),--(Sheet1!$E$2:$E$100="es1100.mail.exchange.co.uk"))

You could make these simpler and more dynamic by adding the test values to the right and pointing there instead.  I've attached a modified version of your file to demonstrate.

NOTES:
1) The message subject text has a leading space and it's included in the above formulas
2) The example formulas only check to row 100; change that value as needed for your actual data.

Regards,
-Glenn
EE-message-tracking-center.xls
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now