I have noticed an interesting quirk with Excel Formulas which I am having a hard time with.
Small Background:
- Dates are in Column A of Data
- Names are in Column B of Data
- Values I am trying to sum up are in Column C of Data
I am trying to sum up all values in C where A matches a set of days and B matches a set of names, both provided in titled LISTS.
To resummarize:
- Criteria #1 is to sum up column C values so long as column A matches a LIST of days
- Criteria #2 is to sum up column C values so long as column B matches a LIST of names
List of Dates is 1 column, called KeyDate List of Names is 1 column, called KeyNames
The formula I am using is:
=sumproduct(sumifs(Data!$C:$C, Data!$A:$A, KeyDate[#Data], Data!$B:$B, KeyNames[#Data]))
What's confusing me is I use a formula like this all the time where I input just one list of stuff (usually dates) using sumproduct(sumifs()). However, the formula clearly breaks down and stops working when I use two lists of stuff. The value I am seeing returned is flat out wrong and it's undercounting by a large amount.
Any advice on how to approach this would be appreciated. Thanks!