Formula to Count Rows Meeting Multiple Criteria, Without Duplicates

[Legacy 276815]

Greetings,

This forum has served me well several times in the past while struggling with Excel, and I'm hoping you guys can come through for me now. No matter how much searching I do for this, the solutions provided to similar problems always boggle my mind a little too much for me to successfully adapt it to my own workbook.

Essentially, I have an excel sheet that is tied to a SQL syslog view populated with the results of numerous jobs I'm deploying to computer workstations in our environment. The resulting source data looks something like:

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Hostname[/TD]
[TD]App[/TD]
[TD]JobType[/TD]
[TD]ReturnCode[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]2014-03-07[/TD]
[TD]01:00:00[/TD]
[TD]ComputerA[/TD]
[TD]X[/TD]
[TD]Install[/TD]
[TD]0[/TD]
[TD]COMPLETE[/TD]
[/TR]
[TR]
[TD]2014-03-07[/TD]
[TD]01:00:00[/TD]
[TD]ComputerA[/TD]
[TD]X[/TD]
[TD]Install[/TD]
[TD]0[/TD]
[TD]COMPLETE[/TD]
[/TR]
[TR]
[TD]2014-03-07[/TD]
[TD]01:00:00[/TD]
[TD]ComputerB[/TD]
[TD]Y[/TD]
[TD]Extract[/TD]
[TD]128[/TD]
[TD]Unzip[/TD]
[/TR]
</tbody>[/TABLE]









Ultimately, I would like to construct a formula that will count something like the below:
The number of rows where App is X, JobType is Install, ReturnCode is 0, and Comment is COMPLETE -- without duplicate results when the Hostname is factored in. I.E., if I re-run a job on the same computer and columns C-G are identical between the two resulting rows, I only want to count this once. In the above example data, the formula should resolve the count as 1 (and not 2).

I'd then use this formula to put together a chart showing the number of "COMPLETE" rows per App with a ReturnCode of 0 vs non-zero (among other things).

I was able to put together a COUNTIFS formula that handily takes care of the multiple criteria requirement, but leaves me completely stumped with how to drop the duplicate results in regards to columns C-G (factoring in the hostname). Any suggestions or example formulas would be GREATLY appreciated!
Current (not quite there) COUNTIFS example: =COUNTIFS(Sheet1!D:D,"X",Sheet1!E:E,"Install",Sheet1!F:F,0,Sheet1!G:G,"COMPLETE")

P.S. If I'm going about this completely backwards, alternate solutions are also totally welcome! The only requirement I was given is that the results should update in real-time as the source data grows, without requiring any user interaction from the person viewing the workbook.

If my issue comes across as confusing just let me know and I'll be happy to clarify further however necessary!

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(C2:C4<>"",IF(D2:D4="X",IF(E2:E4="Install",
    IF(F2:F4=0,IF(G2:G4="COMPLETE",MATCH(C2:C4,C2:C4,0)))))),
    ROW(C2:C4)-ROW(C2)+1),1))

 

[Legacy 276815]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(C2:C4<>"",IF(D2:D4="X",IF(E2:E4="Install",
    IF(F2:F4=0,IF(G2:G4="COMPLETE",MATCH(C2:C4,C2:C4,0)))))),
    ROW(C2:C4)-ROW(C2)+1),1))

You're a wizard, sir. This worked beautifully! Exactly what I needed.
Thank you so much

 

[Aladin Akyurek]

You're a wizard, sir. This worked beautifully! Exactly what I needed.
Thank you so much

You are welcome. Thanks for the nice feedback.