Hello All,
I have attached one excel in which
1. Column I need output as Number of PO delivered ( Example , PO placed (See Column B) on 14-Mar-2014, 24-Apr-2014, 25-Apr-2014 & 17-Nov-2014 are delivered as mentioned in Column C)
Now Column I should show number as 1, 2 and 1 against Mar-14, Apr-14 & Nov-14 respectively.
(We need to Match Column B & Column G and enter count data from Column C in Column I wrt. month)
2. In column J, I want average of PO to Delivery days (Column D) wrt. to Month in Column G matched with months in Column B.
For Ex. Apr-14 should show average of 179, Mar-14 as 41 & Nov-14 as 33.
Please Help.
Regards
Mohammad
Try
=SUMPRODUCT(--(TEXT($B$2:$B$1000,"mmyy")=TEXT($G2,"mmyy")),--($C$2:$C$1000<>""))
and
=IF($I2=0,"",SUMPRODUCT(--(TEXT($B$2:$B$1000,"mmyy")=TEXT($G2,"mmyy")),$D$2:$D$1000)/$I2)
Bookmarks