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

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

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

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

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

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

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

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