问题描述
我正在尝试获得一个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具有日期和名称标准...仅限月份和年份的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!