Sumifs for three different tables

[drag1c]

Hi,
I am using three tables and in one of them I want to use Sumifs formula.

1st table has date of project (the same project is in multiple rows, because using different materials)
2nd table is purchase orders (there is written data for purchases for each material needed. Could have multiple rows of same material)
3rd table is stock level where are no multiple the same rows.

All three tables have material data except in 3rd table there are no duplicate rows.

If current month is January and I have on stock 500 EA of something.
If my project is to be done in March and needs 550 EA of something.
If my Purchase order is to be delivered in February with 70 EA of something.
It should consider I have enough materials.
500+70-550 = 20 EA

Merge3 table is Purchase order table
Merge1 table is Project data
[Material] is from 3rd table where is stock.

This is formula which I've tried:

=SUMIFS(Merge3[Order Quantity],Merge3[Material],[Material],Merge3[Material],Merge1[Material/Asset],Merge3[Deliv. date],"<="&Merge1[Target Date]-120,Merge3[Deliv. date],">="&Merge1[Target Date]-90)

This 120/90 with days is to get date in column not older than 120 days from project table and not younger than 90 days from project table.

In one part of the formula, I am comparing all three tables for Material (...Merge3[Material],[Material],Merge3[Material],Merge[Material/Asset]...)
This formula gives stock of 0 for each item. Where am I mistaken?

 

[jasonb75]

Without the actual tables for reference there is a lot that could easily be misinterpreted in your formula and description.

One thing that does stand out is the use of full table columns as criteria (not criteria ranges), this may be causing some unexpected (although logically correct) behaviour in the formula.
I cannot say for certain without having the tables set up to test the formula but I believe that the formula in the first row will only look at the first row in [Material] and the first row in each of the Merge1 table ranges rather than all rows as you may be expecting it to.

 

[drag1c]

Table 1

Table 2

Table 3

This formula which I've typed goes in Table3[First Month]. Similar formulas would go in Second Month, Third Month and Fourth Month.

So, All three table using the same Material list except in 3rd table there are no repeat rows. That's the reason I am doing calculation in 3rd table.
But, If I think right, I might get also results if I use Table 1 and Table 2, without table 3 (I would anyway get different results for different dates, no?)

 

[jasonb75]

The table headers don't give me anything useful, the problem will lie within the layout of the data in the tables. I suspect that it is a relative referencing issue.
If you want each individual formula to look at all rows in [Material] and Merge1 instead of just a single row then try wrapping the formula in SUM

=SUM(SUMIFS(....))

Note that if you are using a version of excel that is older than 2022 then you will need to array confirm the formula using Ctrl Shift Enter.

 

[drag1c]

Unfortunatelly, I can't share data inside
I've managed to solve it with just table 1 and table 2... Formula the same, since date is changing in each row

Thank you for your time !

Topic can be closed...