Spill range with COUNTIFS of unique rows

Hi,

I have a table of data (column A, B and C) which contains multiple duplicate rows. I want to create a results column M that has COUNTIFS results with criteria for each row in the table, counted as if each of the rows were unique, without duplicates.

I am able to do this in an indirect way, by first creating a spill range (column F, G and H) containing unique values, and then performing the COUNTIFS by referencing this spill range. See results in column K of the attached workbook.

But when I try to create the spill range inside the criteria_range of the COUNTIFS formula, I get #VALUE! errors because COUNTIFS don't accept arrays.

Does anybody have a solution for this? There's also an OR argument inside the formula I am using, and I want to create the results with a spilled range.

Best regards,
Marbleking

No you there is no such way with countifs. In this case you need the helper columns.

Hi Glenn,

Thanks for deleting rows I should have deleted myself before sending the workbook.

The values that are seen in column K are correct.

I have added a column O where the calculations have been performed in the traditional "copy the formula down" way.

This is the formula:

=COUNTIFS($F$2:$F$7,"<="&$A2;$G$2:$G$7,"<="&$B2;$H$2:$H$7,">="&$A2)+COUNTIFS($F$2:$F$7,"<="&$A2;$G$2:$G$7,"<="&$B2;$H$2:$H$7,"")


I have the unique rows from the data table (column A, B and C) in the intermediate table (columns F, G and H). I want to count how many rows in the intermediate table that corresponds to the criteria made up of values from each row in the data table. For the first row in the data table, that will be "how many rows (with its three columns) in the intermediate table corresponds to "<="&$A2 AND "<="&$B2 AND (">="&$A2 OR "") from that row in the data table.

The formula I need will accomplish this without having to use the intermediate formula, but will instead "create a table of unique rows from the data table, and then apply criteria to each of the columns", while spilling the results. I don't think the COUNTIFS will be able to do that, but perhaps some other functions from O365.

Regards,
Marbleking

Thanks all, I'll have use for both the PQ version sometime, as well as the dynamic array formula. As of now, the BYROW and LAMBDA functions aren't available in my O365 at work, unfortunately. So if it's possible to modify it to something that doesn't use these, that would be great.

Regards,
Marbleking

Hi Bo_Ry,

Your fine solution doesn't work in my version of Excel since BYROW and LAMBDA is in beta. Do you have a modified version of the formula that doesn't use beta functions? That'd be great!

Regards,
Marbleking