Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF for summary Table
I am attempting to create a summary sheet for a rather large tracking
worksheet. It track individual complaints and their cost to the company. There are two sheets that I am using Data (where information is entered) and Monthly Summary (speaks for itself) The date of the complaints is logged in the Data sheet. A formula =IF(MONTH(N7)<10,YEAR(N7)&" "&MONTH(N7),YEAR(N7)&" "&MONTH(N7)) breaks down the month and year for the complaint. On the summary tab I have a cell that requires the input of that month and date combiniation. All of the data that I want counted or added up needs to be based on the data relevant to the month. **This means that when I type in the date and month in the summary tab, all of the subsequent data will change. I am looking to count certain occurances (# of complaints in 2008 1 with the reason category Non Sales Admin Pricing) And I am looking to add the dollar value of certain occurances ($ value of all occurances in 2008 1 with the reason category Non Sales Admin Pricing) What I would really like is instead of using the ="Non Sales Admin Pricing" is reference it to a cell so I can autofill certain other cells with the same needs. I have tried to communicate this as well as I can. If you would like to see a copy of the sheet I am more than willing to emaiol it to you. Thanks Jeremy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF for summary Table
OK, so, let's say you have your year/month in column P and your issue type in
column B... Then, in your Summary, say your input cell for month/date is B2 and Your entry for issue type is C2. Then: =sumproduct(--('Data'!P2:P10000=B2),--('Data'!B:B10000=C2)) Gives count of the type you entered during the month entered. Let's say the costs are in coolumn C, then: =sumproduct(--('Data'!P2:P10000=B2),--('Data'!B2:B10000=C2),--('Data'!C2:C10000) Hope that makes sense!! Will provide that value. "Jeremy" wrote: I am attempting to create a summary sheet for a rather large tracking worksheet. It track individual complaints and their cost to the company. There are two sheets that I am using Data (where information is entered) and Monthly Summary (speaks for itself) The date of the complaints is logged in the Data sheet. A formula =IF(MONTH(N7)<10,YEAR(N7)&" "&MONTH(N7),YEAR(N7)&" "&MONTH(N7)) breaks down the month and year for the complaint. On the summary tab I have a cell that requires the input of that month and date combiniation. All of the data that I want counted or added up needs to be based on the data relevant to the month. **This means that when I type in the date and month in the summary tab, all of the subsequent data will change. I am looking to count certain occurances (# of complaints in 2008 1 with the reason category Non Sales Admin Pricing) And I am looking to add the dollar value of certain occurances ($ value of all occurances in 2008 1 with the reason category Non Sales Admin Pricing) What I would really like is instead of using the ="Non Sales Admin Pricing" is reference it to a cell so I can autofill certain other cells with the same needs. I have tried to communicate this as well as I can. If you would like to see a copy of the sheet I am more than willing to emaiol it to you. Thanks Jeremy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF for summary Table
You may be able to use a similar formula in the summary worksheet that spaces
that string the way you want. Then you could use something like this to get the count: =countif(data!a:a,'monthly summary'!x9) And =sumif(data!a:a,'monthly summary'!x9,data!b:B) to sum column B of the data worksheet when the strings match. If you have multiple criteria, you could use use =countifs or =sumifs() in xl2007. Or something like this in any version to get a count: =sumproduct(--(data!a1:a100='monthly summary'!x9), --(data'!b1:b100=z9)) Where z9 held the category. =sumproduct(--(data!a1:a100='monthly summary'!x9), --(data!b1:b100=z9), (data!c1:c100)) to sum the stuff in column c when both the other columns match. Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Jeremy wrote: I am attempting to create a summary sheet for a rather large tracking worksheet. It track individual complaints and their cost to the company. There are two sheets that I am using Data (where information is entered) and Monthly Summary (speaks for itself) The date of the complaints is logged in the Data sheet. A formula =IF(MONTH(N7)<10,YEAR(N7)&" "&MONTH(N7),YEAR(N7)&" "&MONTH(N7)) breaks down the month and year for the complaint. On the summary tab I have a cell that requires the input of that month and date combiniation. All of the data that I want counted or added up needs to be based on the data relevant to the month. **This means that when I type in the date and month in the summary tab, all of the subsequent data will change. I am looking to count certain occurances (# of complaints in 2008 1 with the reason category Non Sales Admin Pricing) And I am looking to add the dollar value of certain occurances ($ value of all occurances in 2008 1 with the reason category Non Sales Admin Pricing) What I would really like is instead of using the ="Non Sales Admin Pricing" is reference it to a cell so I can autofill certain other cells with the same needs. I have tried to communicate this as well as I can. If you would like to see a copy of the sheet I am more than willing to emaiol it to you. Thanks Jeremy -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF for summary Table
Is there an add in that will allow for countifs in xl2003?
"Dave Peterson" wrote: You may be able to use a similar formula in the summary worksheet that spaces that string the way you want. Then you could use something like this to get the count: =countif(data!a:a,'monthly summary'!x9) And =sumif(data!a:a,'monthly summary'!x9,data!b:B) to sum column B of the data worksheet when the strings match. If you have multiple criteria, you could use use =countifs or =sumifs() in xl2007. Or something like this in any version to get a count: =sumproduct(--(data!a1:a100='monthly summary'!x9), --(data'!b1:b100=z9)) Where z9 held the category. =sumproduct(--(data!a1:a100='monthly summary'!x9), --(data!b1:b100=z9), (data!c1:c100)) to sum the stuff in column c when both the other columns match. Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Jeremy wrote: I am attempting to create a summary sheet for a rather large tracking worksheet. It track individual complaints and their cost to the company. There are two sheets that I am using Data (where information is entered) and Monthly Summary (speaks for itself) The date of the complaints is logged in the Data sheet. A formula =IF(MONTH(N7)<10,YEAR(N7)&" "&MONTH(N7),YEAR(N7)&" "&MONTH(N7)) breaks down the month and year for the complaint. On the summary tab I have a cell that requires the input of that month and date combiniation. All of the data that I want counted or added up needs to be based on the data relevant to the month. **This means that when I type in the date and month in the summary tab, all of the subsequent data will change. I am looking to count certain occurances (# of complaints in 2008 1 with the reason category Non Sales Admin Pricing) And I am looking to add the dollar value of certain occurances ($ value of all occurances in 2008 1 with the reason category Non Sales Admin Pricing) What I would really like is instead of using the ="Non Sales Admin Pricing" is reference it to a cell so I can autofill certain other cells with the same needs. I have tried to communicate this as well as I can. If you would like to see a copy of the sheet I am more than willing to emaiol it to you. Thanks Jeremy -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF for summary Table
Not that I know.
But I bet the worksheet functions would be faster. Jeremy wrote: Is there an add in that will allow for countifs in xl2003? "Dave Peterson" wrote: You may be able to use a similar formula in the summary worksheet that spaces that string the way you want. Then you could use something like this to get the count: =countif(data!a:a,'monthly summary'!x9) And =sumif(data!a:a,'monthly summary'!x9,data!b:B) to sum column B of the data worksheet when the strings match. If you have multiple criteria, you could use use =countifs or =sumifs() in xl2007. Or something like this in any version to get a count: =sumproduct(--(data!a1:a100='monthly summary'!x9), --(data'!b1:b100=z9)) Where z9 held the category. =sumproduct(--(data!a1:a100='monthly summary'!x9), --(data!b1:b100=z9), (data!c1:c100)) to sum the stuff in column c when both the other columns match. Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Jeremy wrote: I am attempting to create a summary sheet for a rather large tracking worksheet. It track individual complaints and their cost to the company. There are two sheets that I am using Data (where information is entered) and Monthly Summary (speaks for itself) The date of the complaints is logged in the Data sheet. A formula =IF(MONTH(N7)<10,YEAR(N7)&" "&MONTH(N7),YEAR(N7)&" "&MONTH(N7)) breaks down the month and year for the complaint. On the summary tab I have a cell that requires the input of that month and date combiniation. All of the data that I want counted or added up needs to be based on the data relevant to the month. **This means that when I type in the date and month in the summary tab, all of the subsequent data will change. I am looking to count certain occurances (# of complaints in 2008 1 with the reason category Non Sales Admin Pricing) And I am looking to add the dollar value of certain occurances ($ value of all occurances in 2008 1 with the reason category Non Sales Admin Pricing) What I would really like is instead of using the ="Non Sales Admin Pricing" is reference it to a cell so I can autofill certain other cells with the same needs. I have tried to communicate this as well as I can. If you would like to see a copy of the sheet I am more than willing to emaiol it to you. Thanks Jeremy -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with worksheet table and summary | Excel Discussion (Misc queries) | |||
Pivot Table Summary | Excel Discussion (Misc queries) | |||
Creating summary table from detail table | Excel Worksheet Functions | |||
dynamic summary table | Excel Worksheet Functions | |||
PIVOT TABLE - Summary Table into a Databasae Table. | Excel Worksheet Functions |