Excel Countif使用月份和字母作为标准 [英] Excel Countif using month and letter as Criteria's

问题描述

我有一个销售数据列表，其中包括销售日期和销售类型代码。我正在尝试使用COUNTIFS来计算每个月内特定类型的销售额。

I have a list of sales data that includes the date of the sale and also a sales type code. I'm trying to use COUNTIFS to count how many sales of a specific type there were during each month .

所以例如：

Date.                  Sales Type Code
10/01/2014.                 S
12/01/2014.                 S
15/01/2014.                 O
18/01/2014.                 S
02/02/2014.                 O
08/02/2014.                 S

请给我以下结果：

Code S

January -       3    
February -      1

Code O

January -       1
February -      1

我有公式

COUNTIFS('SALES LEDGER'!A:A,F2,'SALES LEDGER'!C:C,"<"&EOMONTH('MONTH Sales by Sales Type'!$C$1,0)+1)

其中A：A是销售类型代码，F2是我要计算的代码，C：C是日期列表，C1是我想计算的一个月的第一个（即01/01/2014）。

Where A:A is the list of Sales Type Codes, F2 is the code I want to count, C:C is the list of dates and C1 is the first of the month I want to count (ie. 01/01/2014).

1月份工作正常，给我预期的结果。但是，当我将C1更改为01/02/2014/2014时，它将一月份和二月份统计，而不是仅在二月。

This works fine for January, giving me the expected result. But when I change C1 to 01/02/2014, it counts January and February together, rather than just February.

如果有任何人有

谢谢

推荐答案

您的 COUNTIFS 公式指定结束日期，但没有开始日期，如果要计算特定的月份和年份（基于C1），则需要使用C1指定开始日期的另一个标准，即

Your COUNTIFS formula specifies an end date but no start date, if you want to count for a specific month and year (based on C1) you need another criterion to specify the start date using C1, i.e.

= COUNTIFS（'SALES LEDGER'！A：A，F2，'SALES LEDGER'！C：C，& ; EOMONTH（'MONTH Sales by Sales Type'$ C $ 1,0）+ 1，'SALES LEDGER'！C：C，> =&'按销售类型销售的MONTH $！$ C $ 1） code>

=COUNTIFS('SALES LEDGER'!A:A,F2,'SALES LEDGER'!C:C,"<"&EOMONTH('MONTH Sales by Sales Type'!$C$1,0)+1,'SALES LEDGER'!C:C,">="&'MONTH Sales by Sales Type'!$C$1)

这篇关于Excel Countif使用月份和字母作为标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！