| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help required in Formula for date
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
24 File : B
25 Jan Is "Jan" a TEXT string or is it a true Excel date formatted to display only the month name? -- Biff Microsoft 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
On May 22, 1:20*am, "T. Valko" wrote:
24 File : B 25 Jan Is "Jan" a TEXT string or is it a true Excel date formatted to display only the month name? -- Biff Microsoft 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 - Hi Biff, "Jan" is a TEXT string and NOT a true Excel date formatted to display only the month name |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
OK, see Bernie's reply.
-- Biff Microsoft Excel MVP wrote in message ... On May 22, 1:20 am, "T. Valko" wrote: 24 File : B 25 Jan Is "Jan" a TEXT string or is it a true Excel date formatted to display only the month name? -- Biff Microsoft 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 - Hi Biff, "Jan" is a TEXT string and NOT a true Excel date formatted to display only the month name |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
On May 22, 1:41*am, wrote:
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 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.... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
|
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help required in Formula for date
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Required Date picker control | Excel Discussion (Misc queries) | |||
| Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
| Date wise Sum Formula required | Excel Discussion (Misc queries) | |||
| Date formula required | Excel Worksheet Functions | |||
| addition to my date formula...required | Excel Worksheet Functions | |||
Linear Mode
