Search code examples
excelsumifssumproduct

Best way to Summing Values based on date AND row criterion lookup - SumIfs? SumProduct?

I have some dummy data that shows income and expenses for a traveling circus. On the 'Management Cash Flow Projection Sheet' I have daily data of what management reported for those line items. On another sheet I need to dynamically sum those values based on 1). the row line-item; 2). sum values for that lineitem as long as they are between the two dates.

I tried to accomplish this using the following sumifs formula but I am getting a Value error

=SUMIFS('Management Cash Flow Projection'!$H$13:$T$16,'Management Cash Flow Projection'!$H$12:$T$12,"<"&Sheet2!C$4,'Management Cash Flow Projection'!$H$12:$T$12,Sheet2!D$4,'Management Cash Flow Projection'!$E$13:$E$16,Sheet2!B5)

Here is the data in worksheet 'Management Cash Flow Projection' enter image description here

And the desired output New

Solution

  • You cannot have a single row in some criteria, a single column in another, and a 2 dimensional range in another in SUMIFS.

    So we need to choose the row to pass to the SUMIFS with INDEX/MATCH Then we can look at the date row and compare to the correct value row:

    =SUMIFS(INDEX('Management Cash Flow Projection'!$H$13:$T$16,MATCh($B5,'Management Cash Flow Projection'!$E$13:$E$16,0),0),'Management Cash Flow Projection'!$H$12:$T$12,">"&Sheet2!C$4,'Management Cash Flow Projection'!$H$12:$T$12,"<="&Sheet2!D$4)