counting unique values across several criteria and date (month/year)

Greetings!

I need to capture whether or not a group during a specific month/year and CP has had any problems. If the group has had one or more problems, it is counted.

I also need it so if there are no groups that occur in a month/year, the result is a blank "".

I have included a spreadsheet with mock data (on the left) and what the results should be in a table (on the right). I have also included notes explaining the different scenarios.


Thanks for taking the time to assist me!!
Joanne

Hi,

Try this array^** formula in AC8 and copy across and down:

=IF(SUM((TEXT($X$6:$X$47,"mmm yyyy")=$AB8)*($W$6:$W$47=AC$7))=0,"",SUM((TEXT($X$6:$X$47,"mmm yyyy")=$AB8)*($W$6:$W$47=AC$7)*($Z$6:$Z$47="x")*(ROW($W$6:$W$47)-MIN(ROW($W$6:$W$47))+1=IFERROR(MATCH($W$6:$W$47&TRIM(LEFT($Y$6:$Y$47,FIND(" ",$Y$6:$Y$47)))&"x",INDEX($W$6:$W$47&TRIM(LEFT($Y$6:$Y$47,FIND(" ",$Y$6:$Y$47)))&$Z$6:$Z$47,,),0),""))))

Regards


^**Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.