根据其他范围内的条件计算该范围内项目的唯一列表 [英] Counting unique list of items from range based on criteria from other ranges

问题描述

我有一个文件，其数据具有以下格式:

I have a file with data in the following format:

文本值1值2

给出值1和值2满足某些条件，找到所有唯一的文本值.

Given value 1 and value 2 meet some criteria, find all the unique text values.

确切数据如下:

john 10 20
john 15 35
mark 20 10
mark 25 15
tom  25 40
lee  16 50

如果val 1 <= 25且值2 <= 35，则唯一文本数= 2(john和mark) 我必须使用公式而不是过滤器来做到这一点.

If val 1 <=25 and value 2 <=35 the number of unique text = 2 (john and mark) I have to do this using formulas not filters.

我一直在尝试频率，Countifs，sumproducts和其他各种方法的组合，似乎无法满足我的需求.

I've been trying combinations of frequency, countifs, sumproducts and a whole range of other methods and can't seem to hit what I'm looking for.

推荐答案

以下公式将满足您的要求:

The following formula will do what you are asking:

=SUM(IF(FREQUENCY(IF(B2:B7<=25,IF(C2:C7<=35,MATCH(A2:A7,A2:A7,0),""),""),IF(B2:B7<=25,IF(C2:C7<=35,MATCH(A2:A7,A2:A7,0),""),""))>0,1))

这是一个数组公式，因此请按Ctrl-Shift-Enter确认.

This is an array formula so confirm it with Ctrl-Shift-Enter.

我指的是还找到了一个较短的:

=SUM(--(FREQUENCY(IF(B2:B7<=25,IF(C2:C7<=35,COUNTIF(A2:A7,"<"&A2:A7),""),""),COUNTIF(A2:A7,"<"&A2:A7))>0))

在 hre 中找到并修改.

这篇关于根据其他范围内的条件计算该范围内项目的唯一列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！