값 목록이 있고 값이 400을 초과하는 횟수를 계산하고 싶지만 다음 행 값도 이 기준과 일치하면 계산에 포함되어서는 안 됩니다. 이 경우 COUNTIF 수식은 쓸모가 없다고 생각합니다. 더 나은 이해를 위해 첨부된 그림을 참조하시기 바랍니다.
답변1
이 이미지에서는 A2 셀에 기준 값을 입력했습니다.
E열부터 H열까지의 수식은 데이터의 첫 번째 행(스프레드시트의 3행)에 입력된 다음 범위의 맨 아래로 복사됩니다.
E 열에는 이전 행에서 값을 가져오는 수식이 있습니다.
=IF(ISTEXT(B2),0,B2)
F열에는 현재 행(B열)의 값이 A2 셀의 기준보다 크고 이전 행(현재 E열)의 값이 셀 A2의 기준보다 작거나 같은지 여부를 계산하는 수식이 있습니다. 셀 A2. 이 수식은 기준보다 큰 행 그룹이 시작되는 행에서 1을 반환합니다.
=IF(AND(E3<=$A$2,B3>$A$2),1,0)
G 열에는 F 열 값의 누적 합계가 있습니다.
=SUM($F$3:F3)
범위의 첫 번째 참조는 절대 참조로 고정되어 있습니다. 이 수식을 복사하면 합산되는 범위가 늘어나 G열에 표시된 값이 됩니다.
마지막으로 H열에는 B열의 값이 A2 셀의 기준보다 큰 경우 G열의 값이 표시됩니다.
=IF(B3>$A$2,G3,"")
최신 버전의 Office 365를 사용하는 경우 모든 단계를 단일 수식으로 결합할 수 있습니다. 이 수식은 위 이미지의 셀 I2에만 입력됩니다.
=LET(
base,$A$2,
data,$B$3:$B$12,
prev,OFFSET(data,-1,0),
numprev,IF(ISTEXT(prev),0,prev),
grpchg,BYROW(CHOOSE({1,2},data,numprev),LAMBDA(r,IF(AND(INDEX(r,,1)>base,INDEX(r,,2)<=base),1,0))),
runsum,SCAN(0,grpchg,LAMBDA(a,b,a+b)),
output,IF(data>base,runsum,""),
output)