On May 22, 2:48*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
My formula worked with the text string Jan and with dates in B13:B22.

(Note two things - if you really mean a file name File A instead of a sheet
named File A, then you will need to use completely different syntax. And
cell values that look like dates are not always dates - they may be
strings.)

If your dates only look like dates, and are really strings:

=IF(ISBLANK($A$25),"""",SUMPRODUCT(('File
A'!$A$13:$B$22=A28)*(TEXT(DATEVALUE('File A'!$B$13:$B$22),"mmm")=$A$25)))

or this, if they are always strings that are d-mmm-yy or dd-mmm-yy

=IF(ISBLANK($A$25),"",SUMPRODUCT(('File A'!$A$13:$B$22=A28)*(MID('File
A'!$B$13:$B$22,LEN('File A'!$B$13:$B$22)-5,3)=$A$25)))

HTH,
Bernie

wrote in message

...
On May 22, 1:25 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:





Srikanth,

There are a number of ways: to work with your layout:

=IF(ISBLANK($A$25),"""",SUMPRODUCT(('File A'!$A$13:$B
$22=A28)*(TEXT('File A'!$B$13:$B$22,"mmm")=$A$25)))

Note that blank cells in B13:B22 will be treated as January.

HTH,
Bernie
MS Excel MVP

wrote in message

...

Hi,
I have data in 2 files. File A contains data in 2 columns and File B I
have data in 1 column. The first cell in file B is validated. I have
given the examples in the below tables. I am using IF function along
with SUMPRODUCT. The date in File A is in dd/mmm/yy format. My
SUMPRODUCT date format is MMM, I am not able to get the correct
answer. Can i get some help on this please.
Eg:-
File : A
Row # Column A Column B
12 Name Date
13 Anthony 3-Mar-09
14 Bob 5-Apr-09
15 Anthony 4-Apr-09
16 Chris 6-Jan-09
17 Bob 15-Apr-09
18 Anthony 26-Apr-09
19 Anthony 5-May-09
20 Chris 14-May-09
21 Bob 2-Feb-09
22 Anthony 31-Mar-09

24 File : B
25 Jan
26 Column A Column B
27 Name Total Count
28 Anthony "IF(ISBLANK($A$25),"""",SUMPRODUCT(('File A'!$A$13:$B
$22=A28)*('File A'!$B$13:$B$22=$A$25)))
29 Bob
30 Chris

I want the result in Column B of File B in row 28.
Your assiswtance would be of great help.
Thanks in advance!
Srikanth HN- Hide quoted text -

- Show quoted text -

Thanks Bernie, but that formula is not working. I am getting the
resultant as 0.- Hide quoted text -

- Show quoted text -

Thanks again Bernie, I used both the formulas, but somehow my
resultant is #VALUE... Can i send you the file along with the formulas
i have used. Your assistance is appreciated.
Thanks Again!
Srikanth HN