Formula Help
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.
"Scott" wrote:
I am receiving a "#VALUE" response to this formula.
"Mike H" wrote:
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" wrote:
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?
|