我们可以在Excel中的COUNTIFS内嵌一个IF吗？ [英] Can we nest an IF inside a COUNTIFS in Excel?

问题描述

我已经在考勤表上工作，并试图自动生成每月报告。我已经要求

A2中的数组公式是：

<$ p $（$ F $ 5，ROW（$ 1：$ 23）-1（$ F $ 5，ROW（$ 1：$ 23）-1 ，1，1，31），$ I2）））））

数组公式需要定稿使用 Ctrl + Shift + Enter↵。一旦正确输入了第一个单元格，就可以像任何其他公式一样填充或复制。

¹_{某些功能不仅可以接受但欢迎单元格范围是相同数量的呼叫但被转置。如果单元格范围相同的大小，偏移或交错范围也是一种选择。在困难的情况下， TRANSPOSE功能< a>可以是有帮助的。}

I have been working on an attendance sheet and trying to make the monthly reports automatic. I have asked my previous question on the same issue and got the idea to accomplish the task.

But now I have stuck at one place. I have this below formula:

=COUNTIFS(C5:C27,">0", E5:E27,"G", F5:F27,"CAT1")

The value in cell "C" in the above is coming from the below formula (in cell "C")

=IF((COUNTIF(G5:AK5,"p"))>0,1,0)

I had to add this extra column ("C") only to supply input to my fist formula. My question is - "Can we merge the IF function inside the COUNTIFS to get the result in one go and to eliminate the use of an extra column (column C)"?

解决方案

To perform these cell reference acrobatics you will likely need to switch to an array formula. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. A 'helper' column such as you've used in column C can generally reduce calculation cycles and make a worksheet more 'user friendly'.

A COUNTIFS function requires that the ranges being examined are not only the same size but also the same shape. Looking at G5:AK5 is not the same as looking at E5:E35 even though they contain the same number of cells¹.

In the sample data below, you formula is in A1 and uses the 'helper column' C. My array formula is in A2 and does not consider column C ahough it incorporated the logic.

        

The array formula in A2 is:

=SUM(IF(E5:E27 = "G", IF(F5:F27 = "CAT1", SIGN(COUNTIFS(OFFSET($G$5, ROW($1:$23)-1, 1, 1, 31), $I2)))))

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula.

¹_{Some functions not only accept but welcome cell ranges that are the same number of calls but transposed. Offsetting or staggering the ranges is also an option if the cell ranges are the same size. In difficult cases the TRANSPOSE function can be helpful.}

这篇关于我们可以在Excel中的COUNTIFS内嵌一个IF吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！