Excel-SUMIF索引和匹配 [英] Excel - SUMIF INDEX and MATCH

问题描述

我坚持使用sumif公式.我想，如果我使用了索引和匹配的某种混合，我可以使它工作，但在过去一个小时中我一直做不到！

I'm stuck with a sumif formula. I think if I used some mix of index and match I could get it to work but I've been unable to do so for the past hour!

我需要根据门牌号和2个日期从一个数字表中求和.例如，我需要对日期08-05-17和13-05-17之间的1号房的数字求和.

I need to sum from a table of numbers depending on the house number and 2 dates. For example, I need to sum the numbers for house 1 between dates 08-05-17 and 13-05-17.

我以前使用索引和匹配的经验是，我只有每个人都用它来获取单个特定数字.

My previous experience with index and match is that I've only every used it to get a single specific digit.

推荐答案

OFFSET和INDIRECT易失，使用INDEX/MATCH可以实现

OFFSET and INDIRECT are volatile, this is possible with INDEX/MATCH

=SUMIFS(INDEX(Sheet1!$W:$AC,MATCH("House " & B4,Sheet1!$A:$A,0),0),Sheet1!$W$3:$AC$3,"<=" & $AE4,Sheet1!$W$3:$AC$3,">=" & $AD4)

0作为INDEX中的第三个条件，将返回MATCH选择的行中的所有列.

The 0 as the third criterion in the INDEX will return all the columns in the row chosen by the MATCH.

此公式是非易失性和非数组的.

This formula is non volatile and non array.

这篇关于Excel-SUMIF索引和匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！