Assistance needed with the COUNTIF/COUNTIFS function.

[BiomedRoss]

I would love some assistance in creating a formula to calculate a number for one of my reports.

I generate monthly reports with work order failure codes, due dates, and completion dates. I want to count the failure codes that passed (we have multiple passing failure codes), but only if the work orders on that row were closed before the due date.These reports can have hundreds of rows.

I want to count text values in this "column" (D) and I want to specify about 8 different failure codes to count. Each time it attempts to count, I want it to compare the dates in "B2" and "C2". If B2 is <= to C2, and the text in D2 matches one of the criteria it should add it to the count.

This is a little more advanced than I am comfortable with. I'd appreciate any help I can get!

Thanks!!!

[Tony Valko]

If I understand what you want...

Make a list of the failure codes. Assume this list is in the range G2:G6.

Then the formula would be something like this:

=SUMPRODUCT(--(B2:B10<=C2:C10),--ISNUMBER(MATCH(D2:D10,G2:G6,0)))

If this isn't even close to what you had in mind then I think we'll need to see a SMALL sample file with the expected result.

[BiomedRoss]

Thanks for the response! I don't think that is quite what I was aiming for. Here is a brief sample of what I want and what I expect to do.

[Tony Valko]

I don't understand how the failure codes are related.

[BiomedRoss]

Sorry for the delayed response - Let me explain a bit more.

The report needs to counts the number of passing failure codes that were completed before the deadline. I have multiple codes that I want to be considered a "pass".

[Tony Valko]

In the file you show these as your expected results:

14 - PM's Completed/Located before Due Date
6 - PM's Not Located
1 - PM's Completed/Located after Due Date

Can you update the sample file by color coding the data for each of those results?

I'm having a hard time trying to understand what needs to be counted for each of those groups.

[BiomedRoss]

Try this: Copy of Test PM Completion Report.xlsx

[BiomedRoss]

I did some work to make the sheet do the basic logic. Perhaps the formula will help you understand. I created a column (F) that shows if it was counted as a "Pass/located/etc". F24 sums up all of the counted PM's that meet my criteria. I also created a column (G) that counts if it was completed after the due date. This is summed up in F26.
v4 Test PM Completion Report.xlsx

I can build on this, but I'd prefer to make it more simple. Our database generates a report similar to this, and I'd like to have a simple template on another tab to extract the data. It would be better if I could omit the "counted" columns and just have a cell with a single formula.

[Tony Valko]

Try this...

In a range of cells list the error codes that you want to use for the count.

Data Range

	J
1	Error Codes
2	A -PM Adm
3	C -Passed
4	O -Passed - Other
5	P -Passed - Minor Adj
6	R - Repair Required
7	X -Salvaged
8

Then:

Count of PM's Completed/Located before Due Date:

=SUMPRODUCT(--ISNUMBER(MATCH(E2:E22,J2:J7,0)),--(C2:C22<=D2:D22))

Count of PM's Not Located:

=SUM(COUNTIF(E2:E22,{"","L - Not Located"}))

Count of PM's Completed/Located after Due Date:

There is some kind of unseen whitespace character in cells C2:C5. I deleted the contents of those cells then this formula returned the correct result:

=SUMPRODUCT(--(C3:C22>D3:D22))

[BiomedRoss]

I think that's it! I really appreciate the help!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.