如何编写公式以使其在Excel中发生 [英] How to write the formula to make this happen in Excel

问题描述

请参考以下屏幕截图:

假设今天的日期是 2018-4-29 ( dd-mm-yyyy ).有两个用户(用户1 和用户2 ).他们两个都在不同的日期在不同的工作区( Workspace1 ， Workspace 2 和 Workspace 3 )中工作.

Assume that today's date is 29-4-2018 (dd-mm-yyyy). There are two users (User 1 and User 2). Both of them have worked in various Workspaces (Workspace1, Workspace 2 and Workspace 3) on different dates.

我想计算用户在当日日期在各个工作区中完成的工作数量，然后分别在单元格C2和C3中显示计数，以便得到如下结果:

I want to calculate the number of jobs a user has done on today's date in various workspaces and then display the count in cell C2 and C3 respectively so that the result is something like this:

有任何想法如何编写公式来实现此结果?

Any Idea how to write a formula to achieve this result?

P.S.我知道我必须先尝试一下，但是我是一个完全优秀的新手，除了非常基本的公式外，对其他知识了解不多.对于这个，我什至不知道从哪里开始.

P.S. I know I have to try it first, but I a complete newbie to excel and don't know much except for very basic formula. For this one, I don't even know where to start.

万一有人想下载此工作簿

Just in case if anyone wants to download this workbook

推荐答案

在 C2 单元格

=COUNTIFS($B$8:$B$26,B2,$D$8:$D$26,"29-04-2018")+COUNTIFS($E$8:$E$26,B2,$G$8:$G$26,"29-04-2018")+COUNTIFS($H$8:$H$26,B2,$J$8:$J$26,"29-04-2018")

如果实际上是今天，则可以将 29-04-2018 替换为 Today()，因此公式将为

If it is actually today then you can replace 29-04-2018 with Today() so, the formula will be

=COUNTIFS($B$8:$B$26,B2,$D$8:$D$26,TODAY())+COUNTIFS($E$8:$E$26,B2,$G$8:$G$26,TODAY())+COUNTIFS($H$8:$H$26,B2,$J$8:$J$26,TODAY())

=COUNTIFS($B$8:$H$26,B2,$D$8:$J$26,TODAY())

这篇关于如何编写公式以使其在Excel中发生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！