Need to count number of presentations that contain certain criteria excluding duplicates

Hello,

I need to create any sort of formula that will count how many presentations contain certain criteria. Each presentation may have multiple rows of data. The start of each new presentation is indicated by a place marker in the first column. I need to count how many of these contain at least one row that has specific criteria, but I don't want it to count duplicates. For example, if I have 1 presentation that has 3 rows of data and 2 rows contain the criteria I'm looking for, I only want it to count once. I also need it to be able to distinguish between dates as there could be one presentation that was dated 10/1/18 and that should be considered separate from a presentation by the same name dated 9/1/18.

I hope that makes sense, please let me know if it doesn't.

Thank you for your help!

Hello and welcome to the forum.

First, fill in column A with something different for each presentation.

Then you can use this:

=SUM(IF(FREQUENCY(IF((F2:F11="Permanent")*(G2:G11="Critical"), MATCH(A2:A11,A2:A11,0)),ROW(A2:A11)-ROW(A2)+1),1)) Ctrl Shift Enter

See attachment.

I need to keep it as a 0 as with the actual workbook there's about 10 other pages of complex formulas that rely on that "0".

That is an improper way to set up data. If you insist on keeping that column like that, you can create a helper column (let's say in column Z) with this formula:

Z2 =COUNT(A$2:A2)

Then change the formula from post #2 to this:

=SUM(IF(FREQUENCY(IF((F2:F11="Permanent")*(G2:G11="Critical"), MATCH(Z2:Z11,Z2:Z11,0)),ROW(Z2:Z11)-ROW(Z2)+1),1)) Ctrl Shift Enter

That helper column can be hidden if you chose to do so.

I took the project over from somebody who set it up. But in fairness, with over 50,000 lines of data and more added each day, eventually you'd run out of easily enterable combinations for an indicator in column A. I'll give the helper column a try though. Thank you!

Please try and press Ctrl+Shift+Enter

=SUM(IFERROR(--(MATCH(IF(("Permanent"=F2:F11)+("Critical"=G2:G11),D2:D11,""),D2:D11,)=(ROW(D2:D11)-ROW(D1))),))