Countif / Sum including a date range

[djjamesp]

Hi,

I'm trying to get the following (sample data set) to complete a count per month: The data I need to extract is how many jobs each name from column B has issued an abort code (column H) and how many jobs they've been able to save per month (also from column H)

I was able to get the following formulae to work, but this only gives me a rolling total - when I try to add the date range it returns a zero value

=COUNTIFS(B2:B1500,"Bryan", H2:H1500,"<>saved")
=COUNTIFS(B2:B1500,"Bryan", H2:H1500,"saved")

But there has to be a better way with more logic built in, sadly, I cannot find a way through many hours or searching and testing

Please help!

Just a quick edit- this is only a sample data set, I realise the two different spellings of Bryan / Brian, this is all correct in the real data

 

[Joe4]

Welcome to the Board!

See here for how to add date ranges to your COUNTIFS formulas.

How to Use COUNTIFS with a Date Range in Excel

This tutorial explains how to use the COUNTIFS function with a date range in Excel, including an example.

www.statology.org

 

[DanteAmor]

Hi @djjamesp Welcome to the MrExcel forum. Please accept my warmest greetings.

I'm trying to get the following (sample data set) to complete a count per month: The data I need to extract is how many jobs each name from column B has issued an abort code (column H) and how many jobs they've been able to save per month (also from column H)

From your original request I see that you need to count by month, by name, by job aborted and by job saved. In reality they are several combinations of counts.

But there has to be a better way with more logic built in

I suggest you create a pivot table. There you can dynamically count on the different concepts that you need.
I show you some examples:

First, create a pivot table.
- Select your data
- then select Insert > Table from the ribbon.
- In the Create PivotTable dialog box, select where to place the PivotTable
- Click OK
- Move fields: Drag a field to the target area to move it from one area to another. Look at the image below:

In Excel, a slicer is a tool that allows you to filter data in a PivotTable:
To add a slicer
Click anywhere in the PivotTable, then select Insert Slicer from the PivotTable tab.
Select fields, In the Insert Slicers dialog box, check boxes Months fields. Look at image below:

Now, you can count by jobs Saved:

By month:

By Saved, Month and Tech:

By two-month period, Not Save, all Tech:

This way, you can count by the combinations you want.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[kvsrinivasamurthy]

Try.
K2= Start Date, L2 =End Date

=COUNTIFS(B2:B1500,"Bryan", H2:H1500,"<>saved", A2:A1500,">="& K2, A2:A1500,"<="&L2)

=COUNTIFS(B2:B1500,"Bryan", H2:H1500,"saved", A2:A1500,">="& K2, A2:A1500,"<="&L2)

 

[djjamesp]

Thank you all! I like all of these options, I'm going to have a play today and see which one works the best for my needs

I'd tried a pivot table, but adding those extra steps in seems like this will work and will make it easier for others to extract the data. I'll report back later

Thanks again for your help and suggestions