Attached file forum.ozgrid.com/index.php?attachment/63322/ lists sales of a product on various dates.
I need to show total sales only between the Start Date of 1 Oct and the End date of 9 Dec.
Challenge: The product in Col A was originally in packs of one hundred. But between 8 November and 4 December it came in fifties. It then reverted to one hundred.
The formula in J4 calculates correctly the "SUMIFS" the 100-pack product was sold between 1/10 - 7/11
The formula in J6 calculates correctly the "SUMIFS" the 50-pack product was sold between 8/11 - 3/12
The formula in J7 calculates correctly the "SUMIFS" the 100-pack product was sold between 4/12 - 9/12
The formula in J9 SHOULD show sales of packs of 100 by combining the criteria in J4 and J7.
=SUMIFS(C:C,B:B,">="&K2,B:B,"<"&DATE(2014,11,8),B:B,">="&DATE(2014,12,4),B:B,"<="&N2,A:A,806)
(Add the Sales in Col C where the Date in Col B is between 1 Oct - 7 Nov, and between 4 Dec and 9 Dec, where the Product number is 806)
Can't see where I have erred in combining them.
As ever, any suggestions will be received gratefully.
Ochimus