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. |
#7
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. "Glenn" wrote: Try a Pivot Table with Date and Account # as a Row Fields, Error Message1 as Column Field and Data Item. 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
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" 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. "Glenn" wrote: Try a Pivot Table with Date and Account # as a Row Fields, Error Message1 as Column Field and Data Item. 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Scott wrote:
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
I'm thinking that I do have that, but I'm still getting the same error message.
"Glenn" wrote: Scott wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|