Based on three values in one sheet calculate number

[ihb95]

Hey guys,
I have a following problem. I hope somebody can help.

Please look into my attached file. It looks a bit weird because I removed sensible data by just removing the information in the cells.

On the sheet called "test" I have a table. (The file in the attachments called table1)
I have another sheet, the one called ADB, where I have three imporant columns. These are

- Date of training
- Status
- Planned cost

in the "test" sheet you can see cells with the value "#WERT!" . In those cells I want to have costs calculated.
So, let's say if in the ADB-sheet in one row the Date of the Training was in October 2019 and the Status is "3 Ordered" and e.g. the cost is 100€, then I want it
to be added to the cell-value of the test-sheet under the column Okt and in the row "Plan". If there is another row in the ADB-Sheet with the same properties then I want the costs of that one added to the 100€ of before etc. etc.

Plan: "3 ordered" and "4 complete"
Ist: "5 Implemented"
For all other numbers nothing should be calculated.

So basically to narrow it down, my question is:

How can I make a cell calculate things based on three different values in another sheet? Important is also, that the date is depending on the Year as well.

It doesn't matter if it's in Excel Functions or VBA.

Thanks in advance and sorry for my bad english!

[ihb95]

Can nobody help?

[davsth]

I am sure they can
you need the dates for October 19 to be proper dates in the test sheet if d10 was october 10 (1/10/19)

then =SUMIFS(ADB!$T:$T,ADB!$R:$R,">="&test!D$10,ADB!$R:$R,"<"&EDATE(test!D$10,1),ADB!$U:$U,"3 Ordered")+SUMIFS(ADB!$T:$T,ADB!$R:$R,">="&test!D$10,ADB!$R:$R,"<"&EDATE(test!D$10,1),ADB!$U:$U,"4 confirmed")

or
=SUM(SUMIFS(ADB!T:T,ADB!$R:$R,">="&test!D$10,ADB!$R:$R,"<"&EDATE(test!D$10,1),ADB!$U:$U,{"3 Ordered","4 Confirmed"}))

may be what you are looking for (you may have to switch , for ; depending on your region
you also say complete when your data says confirmed

Any closer?

[ihb95]

Hey davsth,

thanks for your reply. I receive #NAME? in the cells when I put that in. I am not entirely sure in which cell I am supposed to put that formula also.

Thanks for the advice with the ";". I needed to change it :-D
So right now I am a bit confused about where to put the formula and how to change the dates. Because I want table to react dynamically t5o a change in the year (e.g. 15/16)

thank you already!

[davsth]

What is unclear is how you update the dates

D10 needs to update from someother selection, it might be =d6 or something else, you have not explained!
e11 could be edate(d10,1) anf this can be copied in all the cells to the right (dates can be formated as MMM if you do not want the year to appear, I assume the labels are the short month in german!)

the formula I gave goes in D12 and can be copied to the right

=SUM(SUMIFS(ADB!$T:$T,ADB!$R:$R,">="&test!D$10,ADB!$R:$R,"<"&EDATE(test!D$10,1),ADB!$U:$U,{"3 Ordered","4 Confirmed"}))

Any closer?

[ihb95]

Okay, sorry for not specifying enough!

So the year is in D4, which is a dropdown list that takes data from the parameter sheet. You are right, the labels are the shorts for the months in german.
Somehow I think the comparison between the dates and D10 is what is not working right now for me. Does it work if you test it in the file?
It might be the formatting, I am trying every single format right now
im sorry for being so dumb with excel

[davsth]

The date cells may need work on, as it is not clear which referrence to link for the year

[ihb95]

Hey davsth,

thank you for the response! I am currently not in office to check it so I will need to reply you in two days.
I hope this can wait for that time

THank you sooo much already!!