Formula Help

[Scott]

I have a spreadsheet with multiple column data sorted by date. I am looking
to create a report that will count the number of text occurances on a given
date. For instance, there are 36 occurances of the search criteria in Column
B on January 1st. Any ideas?

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((A1:A100=DATE(2008,1,1))*(B1:B100="My Search Criteria"))

or if you search criteria is a number

=SUMPRODUCT((A1:A00=DATE(2008,1,1))*(B1:B00=nnn))

It would hawever be far more flexible if you used cell references in your
formula

=SUMPRODUCT((A1:A00=C1)*(B1:B00=C2))
Where C1 is the date and C2 is your search criteria.

Mike

 

[Scott]

I am receiving a "#VALUE" response to this formula.

 

[Scott]

Let me clarify a little more about what I'm trying to achieve. In a
workbook, I have a worksheet with a table of data. On a seperate worksheet
in the workbook, I want to create a report analyzing the data. For instance,
in the worksheet I have data broken down like this:

"Date" "Account #" "Error Message1"
1/1/2008 12345678 Failed
1/1/2008 87654321 Failed
1/2/2008 12345678 Failed
1/3/2008 12345678 Passed

I want to create a report that will count the total number of "Failed"
responses in the "ErrorMessage1" column on 1/1/2008, on 1/2/2008, on
1/3/2008, etc. With the number and types of errors on each specified date.

 

[Glenn]

Let me clarify a little more about what I'm trying to achieve. In a
workbook, I have a worksheet with a table of data. On a seperate worksheet
in the workbook, I want to create a report analyzing the data. For instance,
in the worksheet I have data broken down like this:

"Date" "Account #" "Error Message1"
1/1/2008 12345678 Failed
1/1/2008 87654321 Failed
1/2/2008 12345678 Failed
1/3/2008 12345678 Passed

I want to create a report that will count the total number of "Failed"
responses in the "ErrorMessage1" column on 1/1/2008, on 1/2/2008, on
1/3/2008, etc. With the number and types of errors on each specified date.

Try a Pivot Table with Date and Account # as a Row Fields, Error Message1 as
Column Field and Data Item.

 

[Scott]

That doesn't seem to be working either. I can calculate the totals that I'm
looking for without specifying a date, but I want to track by a date.

 

[Glenn]

Scott wrote:
That doesn't seem to be working either. I can calculate the totals that I'm
looking for without specifying a date, but I want to track by a date.

What do you mean by "doesn't work"? When I do a Pivot Table using your sample
data as instructed above (without the Account # in the Row Field), I get
something like the following:


Error Message1
Date Failed Passed Grand Total
1/1/2008 2 2
1/2/2008 1 1
1/3/2008 1 1
Grand Total 3 1 4



If that's not close to what you want, then I would need to see an example of
what you are looking for to help further.

 

[Scott]

I get an error messsage: "The PivotTable field name is not valid. To create
a PivotTable report, you must use data that is organized as a list with
labeled columns. If you are changing a name of a PivotTable field, you must
type a new name for the field."

The data is recorded in the list. I am trying to use data from "'Error
List'!A1:I50000" Granted, I'm not as familiar with PivotTables as I am with
other functions.

 

[Glenn]

I get an error messsage: "The PivotTable field name is not valid. To create
a PivotTable report, you must use data that is organized as a list with
labeled columns. If you are changing a name of a PivotTable field, you must
type a new name for the field."

The data is recorded in the list. I am trying to use data from "'Error
List'!A1:I50000" Granted, I'm not as familiar with PivotTables as I am with
other functions.

Make sure there is a valid heading in all cells in Row 1.

 

[Scott]

I'm thinking that I do have that, but I'm still getting the same error message.