Vlookup & count

[zaid]

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

[Bob Phillips]

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)