Counting Data in the Current Month

See the attached spreadsheet. It counts incidents and high incidents by customer. "Sheet1" references the "Log" tab. The "Log" tab allows values from the "List" Tab.

I would like to create a formula to count:

- Column D: Number of "Incidents" in current month (May only). Refer to Column A for that classification of "Incident".
- Column E: Number of "High Incidents" in current month (May only). Refer to Column B for that classification of "High Incident".

I put the expected value in the column.


Also, when I attempt the data by the highest total (for example, by Column D where Customer2 has the most "High Incidents", the formula's don't track and the results by Customer are no longer correct. Any idea on how to fix that?

Originally Posted by MarvinP

Hi bdav,

This looks like a Pivot Table answer to me. See the attached and the Pivot on the Log table. This is how I'd do this problem.

Attachment 519218

Thank you, I will review.

When you created the PIVOT:

How did you select the "May" month?
Where does the column 'total' come from?

This looks good at first glance.

Try

In D7

=COUNTIFS(Log!$A:$A,Sheet1!$A7,Log!$C:$C,"Incident",Log!$E:$E,">=" &EOMONTH(TODAY(),-1)+1,Log!$E:$E,"<=" &EOMONTH(TODAY(),0))

in E7

=COUNTIFS(Log!$A:$A,Sheet1!$A7,Log!$C:$C,"High Incident",Log!$E:$E,">=" &EOMONTH(TODAY(),-1)+1,Log!$E:$E,"<=" &EOMONTH(TODAY(),0))

You can adapt these for 3 and 6 month periods

=COUNTIFS(Log!$A:$A,Sheet1!$A7,Log!$C:$C,"Incident",Log!$E:$E,">=" &EOMONTH(TODAY(),-3)+1,Log!$E:$E,"<=" &EOMONTH(TODAY(),0))

will give results from beginning of March

Originally Posted by MarvinP

I put the date in the Filter and counted customers. You need to play with Pivots a bit to see how easy they are. No formulas needed!

How did you convert the 'Incident Date' values into a month value? (example 'May' vs. having every possible date being displayed).

Also, in the 'log' tab where the data is being sourced from for the pivot table, is there a way to refresh/account for new rows of data being added?

Basically the 'incident date' field looks like this: 05/04/2017
The pivot filter section has only the month being displayed in the filter: May

The pivot looks great, it's difficult to select all of the days vs. just selecting a month, but I can't figure out how that was changed.

Originally Posted by oeldere

Make a table of the data, then new data (inserted in the next row of that table) will automatic be part of the table.

Insert => table.



I am not able to open the added files in this topic, so I can't see what you mean.

How do I convert my data into a table? Do I still have capability to add new rows or is there some method to adding new rows to a table?

To go back to the pivot table:

Currently, there are dates like this:
05/16/2017
05/01/2017

In the pivot example that was provided, those dates were converted to the month: 'May'.

I have checked the formatting of the pivot and don't see how this is being converted.

Any idea on how to update the format of the field for the pivot only? There is an example above.

One final update to the attached. When Column G of the 'Log Tab' is = 'BBB', count. If the value of Column G is equal to something other than 'BBB', do not count.

In the attached example, there are examples in Column G of 'AAA'. Those examples should not be counted.

I need this logic in Column B, Column C and Columns D-I, but I can get an example of Column B, Column C and Column D, I can apply the rest of the logic.

thank you!

Just add the following:

=COUNTIFS(Log!$A:$A,Sheet1!$A7,Log!$G:$G,"BBB",Log!C:C,"High Incident")

to each formula

Originally Posted by JohnTopley

Just add the following:

=COUNTIFS(Log!$A:$A,Sheet1!$A7,Log!$G:$G,"BBB",Log!C:C,"High Incident")

to each formula

Thank you! One last question. How do I also say Not Equal to "BBB" with that same formula? There are times I want to count only "BBB" and other scenario's where I want to count all other values except for "BBB" with all of the other logic the same.

The "not Equal" is "<>"

Originally Posted by JohnTopley

The "not Equal" is "<>"

Log!$G:$G,"<> BBB"

Does this look ok?

My last question is this -> When I sort the data, all of my formula's get messed up. Do I need to make an update to account for the formula when sorting?

Here are my current formula's that I am attempting to sort:

Primary Sort is Column C of my Customers Tab:
=COUNTIFS(Log!$A:$A,'Customers'!$A6,Log!$I:$I,"BBB",Log!$D:$D,"Incident")

Secondary Sort is Column A of my Customers Tab where the customers are listed:
Customer A
Customer B

Prior to sorting, if Customer A has a value of 3 and Customer B has a value of 5, after I sort, the values get flipped.

Please post a file as all the columns have changed.

The sorting of the columns should have no bearing on the result as it will simple match CUSTOMERS!A6 wherever this customer exits in the file.

You need to ensure when you sort ALL columns are selected prior to sorting.

Originally Posted by JohnTopley

Please post a file as all the columns have changed.

The sorting of the columns should have no bearing on the result as it will simple match CUSTOMERS!A6 wherever this customer exits in the file.

You need to ensure when you sort ALL columns are selected prior to sorting.

See attached. When I attempt to sort by Column C (or any of the columns), the data is no longer linked to the customer.

Also, my counter of non-BBB (Column D) appears to be counting ALL instances instead of just the non-BBB.

Thank you for taking a look!

Column D formula incorrect as it has additional space

=COUNTIFS(Log!$A:$A,'BBB Customers'!$A6,Log!$I:$I,"<>BBB")

not

=COUNTIFS(Log!$A:$A,'BBB Customers'!$A6,Log!$I:$I,"<> BBB")

Sorted works for me if you select ALL columns then select columns to sort (use Custom Sort")