| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement
In my Excel spreadsheet I am trying to figure out a formula to combine an
examiner's initials with a specific date in which a record was audited. For Example, part of my spreadsheet looks something like this: Date Examiner Audited ds 04/03/07 ds 04/03/07 ds 03/31/07 tc 03/31/07 tc 03/29/07 hr 04/04/07 At the bottom of my spreadsheet I want to provide a brief summary on how many audits were made by each examiner on a specific date. ds would have 2 on 04/03/07, I want the intials of the examiner and the date with a count of the date. I have a large spreasheet, this is just two columns out of many. Appreciate any help! JS |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement
=SUMPRODUCT(--(A1:A100="ds"),--(B1:B100="04/03/07")) will give you a count of
all rows for which Column A = ds and Column B = 4/3/07. You may also want to look at pivot tables to summarize this kind of data. See here for info on pivot tables: http://www.cpearson.com/excel/pivots.htm Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "JS" wrote: In my Excel spreadsheet I am trying to figure out a formula to combine an examiner's initials with a specific date in which a record was audited. For Example, part of my spreadsheet looks something like this: Date Examiner Audited ds 04/03/07 ds 04/03/07 ds 03/31/07 tc 03/31/07 tc 03/29/07 hr 04/04/07 At the bottom of my spreadsheet I want to provide a brief summary on how many audits were made by each examiner on a specific date. ds would have 2 on 04/03/07, I want the intials of the examiner and the date with a count of the date. I have a large spreasheet, this is just two columns out of many. Appreciate any help! JS |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement
Thanks Dave. I like this approach using the Pivot table. I never thought of
that. I don't want to have to type in all the dates in the formula. I was trying to use the VLOOKUP but that wasn't working. Thanks! "Dave F" wrote: =SUMPRODUCT(--(A1:A100="ds"),--(B1:B100="04/03/07")) will give you a count of all rows for which Column A = ds and Column B = 4/3/07. You may also want to look at pivot tables to summarize this kind of data. See here for info on pivot tables: http://www.cpearson.com/excel/pivots.htm Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "JS" wrote: In my Excel spreadsheet I am trying to figure out a formula to combine an examiner's initials with a specific date in which a record was audited. For Example, part of my spreadsheet looks something like this: Date Examiner Audited ds 04/03/07 ds 04/03/07 ds 03/31/07 tc 03/31/07 tc 03/29/07 hr 04/04/07 At the bottom of my spreadsheet I want to provide a brief summary on how many audits were made by each examiner on a specific date. ds would have 2 on 04/03/07, I want the intials of the examiner and the date with a count of the date. I have a large spreasheet, this is just two columns out of many. Appreciate any help! JS |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement
Just a warning if those values are really dates:
=SUMPRODUCT(--(A1:A100="ds"),--(B1:B100=date(2007,4,3)) (for April 3rd) Dave F wrote: =SUMPRODUCT(--(A1:A100="ds"),--(B1:B100="04/03/07")) will give you a count of all rows for which Column A = ds and Column B = 4/3/07. You may also want to look at pivot tables to summarize this kind of data. See here for info on pivot tables: http://www.cpearson.com/excel/pivots.htm Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "JS" wrote: In my Excel spreadsheet I am trying to figure out a formula to combine an examiner's initials with a specific date in which a record was audited. For Example, part of my spreadsheet looks something like this: Date Examiner Audited ds 04/03/07 ds 04/03/07 ds 03/31/07 tc 03/31/07 tc 03/29/07 hr 04/04/07 At the bottom of my spreadsheet I want to provide a brief summary on how many audits were made by each examiner on a specific date. ds would have 2 on 04/03/07, I want the intials of the examiner and the date with a count of the date. I have a large spreasheet, this is just two columns out of many. Appreciate any help! JS -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement
Yes, you're right. Yet another reason to use pivot tables for this kind of
summary! Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Dave Peterson" wrote: Just a warning if those values are really dates: =SUMPRODUCT(--(A1:A100="ds"),--(B1:B100=date(2007,4,3)) (for April 3rd) Dave F wrote: =SUMPRODUCT(--(A1:A100="ds"),--(B1:B100="04/03/07")) will give you a count of all rows for which Column A = ds and Column B = 4/3/07. You may also want to look at pivot tables to summarize this kind of data. See here for info on pivot tables: http://www.cpearson.com/excel/pivots.htm Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "JS" wrote: In my Excel spreadsheet I am trying to figure out a formula to combine an examiner's initials with a specific date in which a record was audited. For Example, part of my spreadsheet looks something like this: Date Examiner Audited ds 04/03/07 ds 04/03/07 ds 03/31/07 tc 03/31/07 tc 03/29/07 hr 04/04/07 At the bottom of my spreadsheet I want to provide a brief summary on how many audits were made by each examiner on a specific date. ds would have 2 on 04/03/07, I want the intials of the examiner and the date with a count of the date. I have a large spreasheet, this is just two columns out of many. Appreciate any help! JS -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement
Thanks Dave. I tried all the suggested formulas and nothing worked. I saw
it wasn't recognizing the date as a date. I could see it was not pulling a date entry but a strange number. Let me try =date in my formula. I'm sure this is the problem. Thanks everyone! I appreciate all the input and knowledge!! "Dave Peterson" wrote: Just a warning if those values are really dates: =SUMPRODUCT(--(A1:A100="ds"),--(B1:B100=date(2007,4,3)) (for April 3rd) Dave F wrote: =SUMPRODUCT(--(A1:A100="ds"),--(B1:B100="04/03/07")) will give you a count of all rows for which Column A = ds and Column B = 4/3/07. You may also want to look at pivot tables to summarize this kind of data. See here for info on pivot tables: http://www.cpearson.com/excel/pivots.htm Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "JS" wrote: In my Excel spreadsheet I am trying to figure out a formula to combine an examiner's initials with a specific date in which a record was audited. For Example, part of my spreadsheet looks something like this: Date Examiner Audited ds 04/03/07 ds 04/03/07 ds 03/31/07 tc 03/31/07 tc 03/29/07 hr 04/04/07 At the bottom of my spreadsheet I want to provide a brief summary on how many audits were made by each examiner on a specific date. ds would have 2 on 04/03/07, I want the intials of the examiner and the date with a count of the date. I have a large spreasheet, this is just two columns out of many. Appreciate any help! JS -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement
For XL2007
=COUNTIFS(A2:A100,"ds",B2:B100,"4/3/2007") "JS" wrote: In my Excel spreadsheet I am trying to figure out a formula to combine an examiner's initials with a specific date in which a record was audited. For Example, part of my spreadsheet looks something like this: Date Examiner Audited ds 04/03/07 ds 04/03/07 ds 03/31/07 tc 03/31/07 tc 03/29/07 hr 04/04/07 At the bottom of my spreadsheet I want to provide a brief summary on how many audits were made by each examiner on a specific date. ds would have 2 on 04/03/07, I want the intials of the examiner and the date with a count of the date. I have a large spreasheet, this is just two columns out of many. Appreciate any help! JS |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Statement
Or maybe...
=COUNTIFS(A2:A100,"ds",B2:B100,date(2007,4,3)) Teethless mama wrote: For XL2007 =COUNTIFS(A2:A100,"ds",B2:B100,"4/3/2007") "JS" wrote: In my Excel spreadsheet I am trying to figure out a formula to combine an examiner's initials with a specific date in which a record was audited. For Example, part of my spreadsheet looks something like this: Date Examiner Audited ds 04/03/07 ds 04/03/07 ds 03/31/07 tc 03/31/07 tc 03/29/07 hr 04/04/07 At the bottom of my spreadsheet I want to provide a brief summary on how many audits were made by each examiner on a specific date. ds would have 2 on 04/03/07, I want the intials of the examiner and the date with a count of the date. I have a large spreasheet, this is just two columns out of many. Appreciate any help! JS -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF and AND statement | Excel Worksheet Functions | |||
| appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
| If Statement | Excel Discussion (Misc queries) | |||
| If statement and Isblank statement | Excel Worksheet Functions | |||
| Help please, IF statement/SUMIF statement | Excel Worksheet Functions | |||
Hybrid Mode
