Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Scott wrote:
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
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" wrote: Scott wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|