Help required in Formula for date

[hn.srikanth]

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

 

[T. Valko]

24 File : B

25 Jan

Is "Jan" a TEXT string or is it a true Excel date formatted to display only
the month name?

 

[Bernie Deitrick]

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

 

[hn.srikanth]

Is "Jan" a TEXT string or is it a true Excel date formatted to display only
the month name?

--
Biff
Microsoft Excel MVP








- Show quoted text -

Hi Biff,
"Jan" is a TEXT string and NOT a true Excel date formatted to display
only

 

[T. Valko]

OK, see Bernie's reply.

--
Biff
Microsoft Excel MVP

Is "Jan" a TEXT string or is it a true Excel date formatted to display
only
the month name?

--
Biff
Microsoft Excel MVP








- Show quoted text -

Hi Biff,
"Jan" is a TEXT string and NOT a true Excel date formatted to display
only

 

[hn.srikanth]

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








- Show quoted text -

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

 

[hn.srikanth]

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

- Show quoted tex

I am sorry if my questin is confusing. In File A Column B the date is
in dd-mmm-yyyy format. But in File B cell Column A cell 25 Month "Jan"
is text. Based on cell 25 i want sum product function from File A
column B....

 

[Bernie Deitrick]

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

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








- Show quoted text -

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

 

[hn.srikanth]

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










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

 

[Bernie Deitrick]

Srikanth,

Sure - send away. Make the obvious changes to my email address.

HTH,
Bernie
MS Excel MVP

- 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

 

[hn.srikanth]

Sure Bernie, Thanks! I have sent the file to (e-mail address removed).
Please let me know if this is not ht ecorrect email ID.

 

[Bernie Deitrick]

If you are following this thread, the first formula that I posted:

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

was what worked, since the date values were actual dates.

HTH,
Bernie
MS Excel MVP