SUMIFS具有日期和名称标准...仅限月份和年份 [英] SUMIFS with date and name criteria... month and year only

问题描述

我正在尝试获得一个SUMIFS公式来检查日期列，并且只求出与标准日期匹配的年份和月份相对应的值。我也希望这个SUMIFS包括一个名称标准以及日期。即

I am trying to get a SUMIFS formula to check a column of dates and sum only the values that correspond to the matching year and month of the criterion date. I would also like this SUMIFS to include a name criterion along with the date. i.e.

单元格A1： = SUMIFS（'Sheet1'！O：O，'Sheet1'！D：D，'Sheet2'！DATE B2），'Sheet1'！E：E，名称）

sheet1列O是存储和值的位置

sheet1列D是存储日期值的地方

sheet2 cell B2是存储日期比较条件的地方

sheet1列E是存储名称的位置>
名称是我想要的总和选择的名称标准

sheet1 column O is where the sum values are stored
sheet1 column D is where the date values are stored
sheet2 cell B2 is where the date comparison criterion is stored
sheet1 column E is where the names are stored
"Name" is the name criterion that I want for sum selection

任何见解将非常感谢！

推荐答案

如果您创建了日期的开始和结束日期，您可以使用 SUMIFS ，即使用此版本

You can use SUMIFS if you create a start and end date for your dates, i.e. with this version

=SUMIFS('Sheet1'!O:O,'Sheet1'!D:D, ">="&EOMONTH('Sheet2'!B2,-1)+1, 'Sheet1'!D:D, "<"&EOMONTH('Sheet2'!B2,0)+1, 'Sheet1'!E:E, "Name")

EOMONTH 用于获取相关的开始和结束日期根据您的其他条件，您的 SUMIFS 将正确的值相加。

EOMONTH is used to get the start and end dates of the relevant month then your SUMIFS sums the correct values based on your other criteria.

如果 B2 保证是您可以省略第一个 EOMONTH 功能

If B2 is guaranteed to be the first of the month you can omit the first EOMONTH function

这篇关于SUMIFS具有日期和名称标准...仅限月份和年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！