如何在Excel中编写Countifs公式？

When I drag or copy the formula to another cell then the date should be changed. Every time I need to change the date manually.

当我将公式拖动或复制到另一个单元格时,应该更改日期。每次我需要手动更改日期。

Only date should change when I drag the formula as this is a monthly.

拖动公式时,只有日期应该更改,因为这是每月一次。

Example is given below.

实例如下。

=COUNTIFS($M$35:$M$1007,”=1/9/2018″,$N$35:$N$1007,”TS”,$O$35:$O$1007,”HP”,$P$35:$P$1007,”1″)
=COUNTIFS($M$35:$M$1007,”=2/9/2018″,$N$35:$N$1007,”TS”,$O$35:$O$1007,”HP”,$P$35:$P$1007,”1″)
=COUNTIFS($M$35:$M$1007,”=3/9/2018″,$N$35:$N$1007,”TS”,$O$35:$O$1007,”HP”,$P$35:$P$1007,”1″)

2 个解决方案

#1

Use the DATE function to return a true date. The DATEVALUE function is an alternative but I prefer DATE. Don't quote numbers. Try not to use ambiguous DMY/MDY dates for examples. You don't need equals (e.g. =) signs in a COUNTIFS.

使用DATE函数返回真实日期。 DATEVALUE函数是另一种选择,但我更喜欢DATE。不引用数字。尽量不要使用含糊不清的DMY / MDY日期作为示例。您在COUNTIFS中不需要等号(例如=)。

Quoted strings do not change when dragged or copied to a new location. You need to include something that changes.

拖动或复制到新位置时,带引号的字符串不会更改。你需要包含一些变化的东西。

I've assumed a DMY date system. For 01-Sep-2018,

我假设有一个DMY日期系统。 2018年9月1日,

=COUNTIFS($M$35:$M$1007, date(2018, 9, row(1:1)), $N$35:$N$1007, "TS", $O$35:$O$1007, "HP", $P$35:$P$1007, 1)

Fill down for 02-Sep-2018, 03-Sep-2018, etc.

2018年9月2日,2018年9月3日,等等。

Alternative for dragging across.

拖拽的替代方案。

=COUNTIFS($M$35:$M$1007, date(2018, 9, column(a:a)), $N$35:$N$1007, "TS", $O$35:$O$1007, "HP", $P$35:$P$1007, 1)

Be careful about putting 'smart quote' in your formulas. Your examples were littered with them and they don't work in formulas.

在公式中加入“智能引用”时要小心。你的例子充满了它们,它们在公式中不起作用。

#2

You would need to define the dates you care about in their own column, and then make sure that the reference to them is unlocked within the formula:

您需要在自己的列中定义您关心的日期,然后确保在公式中解锁对它们的引用:

 'A'         'B'
|date_field| formula                                                 |
----------- ----------------------------------------------------------
| 1/9/2018 |=COUNTIFS($M$35:$M$1007,$A1,$N$35:$N$1007,"TS", ... etc) |
----------- ----------------------------------------------------------
| 2/9/2018 |=COUNTIFS($M$35:$M$1007,$A2,$N$35:$N$1007,"TS", ... etc) |
----------- ----------------------------------------------------------
| 3/9/2018 |=COUNTIFS($M$35:$M$1007,$A3,$N$35:$N$1007,"TS", ... etc) |
----------- ----------------------------------------------------------

#1