Dynamic range from another sheet

[johnny70]

Dynamic range?!
I´ll try to give an example on what I would like to do:

Sheet A, my data containing sheet fill in e.g. orders, date of order, accounts, invoice dates, cost etc e.g:
Row Order no Account Company Order date Order sum payment plan date actual invoice date actual invoice cost
97 711364-000 156010112461 Rexel 2017-02-08 357,72 sek 2018-02-15 2017-02-09 357,72 kr


Sheet B contains the summary for each year, month and accounts e.g:
In cell AV7 I have written used this formula to extract correct accounts, year and month: =SUMPRODUCT((MONTH(Orders!$H$2:$H$97)=AV$6)*(YEAR(Orders!$H$2:$H$97)=$AV$4)*(Orders!$C$2:$C$97=$D$7)*(Orders!$G$2:$G$97))
If I then fill in a new order, row 98 I would like to automatically get the bold numbers, end of Range to be updated to 98

I would like perform this without using macro if psosible, how?

 

[steve the fish]

See if this produces the same results:

=SUMIFS(Orders!$G:$G,Orders!$H:$H,">="&DATE($AV$4,$AV$6,1),Orders!$H:$H,"<"&DATE($AV$4,$AV$6+1,1),Orders!$C:$C,$D$7)

Sumifs is ok with full column references.

 

[johnny70]

Problem is that I also have som subtotal summary in my data sheet that I would like to keep, currently this has given me some problem when using the "H:H" solution

 

[steve the fish]

So did the solution i gave work? Do other rows in the data satisfy the conditions?

 

[johnny70]

I think I need some explanation how your solution is meant to work, currently all cell gives the value 0

 

[steve the fish]

What does this produce:

=SUMPRODUCT((MONTH(Orders!$H$2:$H$97)=AV$6)*(YEAR(Orders!$H$2:$H$97)=$AV$4)*(Orders!$C$2:$C$97=$D$7)*(Orders!$G$2:$G$97))

because the sumifs and the sumproduct are essentially the same bar the length of the arrays.

 

[johnny70]

Intention from my side was to compare e.g. Order row 2, Cell H2 (prognosis) in Sheet A with e.g. column in Sheet B, If the month is the correct (Cell AV6) and equal to january and if year (Cell AV4) is correct IF true also verify that account is correct in Sheet A row 2 (Cell C2) is the same as in Sheet B (Cell D7), IF true then I can add Cell value from Sheet A cell G2 to the sum in Sheet B cell AV7. This adding of sum in Sheet B, cell AV7 should be performed for all rows in Cell A that contains figures and fulfill the formula of year, month and account

Tricky to describe my intention in a good way..
I also have some summary cells and Headlines in sheet A to take into consideration

 

[steve the fish]

What your sumproduct is doing is summing up orders G2:G97 where C2:C97 = D7, and the date in H2:H97 has the same month number as in AV6 and the same year number as in AV4. That is the same as the sumifs i provided is doing except it uses full column references which gets around your initial problem. The only thing i can think of if the sumifs produces 0 whilst the sumproduct doesnt is that your dates arent really dates but text.