| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting data that meets 3 different Criteria
Hi,
Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
Maybe...
=sumproduct(--(sheet1!a1:a100="accident type 1"), --(text(sheet1!b1:b100,"yyyymm")="200907"), --(sheet1!c1:c100="dept number here")) 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 ============ But you may want to look into a pivottable. If your data is laid out nicely (and it sounds like it is), you could get some very nice summary reports pretty quickly. Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Jenny.S wrote: Hi, Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
Thanks, have had a play with pivot tables.
I have named ranges in my spreadsheet, i.e: "M" is the column representing the month, "TD" is department and "TI" is injury type. I am having trouble with getting the =sumproduct to work and to put the results in a different tab. Do you have any suggestions? Thanks, Jenny.S "Dave Peterson" wrote: Maybe... =sumproduct(--(sheet1!a1:a100="accident type 1"), --(text(sheet1!b1:b100,"yyyymm")="200907"), --(sheet1!c1:c100="dept number here")) 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 ============ But you may want to look into a pivottable. If your data is laid out nicely (and it sounds like it is), you could get some very nice summary reports pretty quickly. Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Jenny.S wrote: Hi, Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
Share the addresses of the ranges that contain each of the categories.
Share the formula you tried. Jenny.S wrote: Thanks, have had a play with pivot tables. I have named ranges in my spreadsheet, i.e: "M" is the column representing the month, "TD" is department and "TI" is injury type. I am having trouble with getting the =sumproduct to work and to put the results in a different tab. Do you have any suggestions? Thanks, Jenny.S "Dave Peterson" wrote: Maybe... =sumproduct(--(sheet1!a1:a100="accident type 1"), --(text(sheet1!b1:b100,"yyyymm")="200907"), --(sheet1!c1:c100="dept number here")) 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 ============ But you may want to look into a pivottable. If your data is laid out nicely (and it sounds like it is), you could get some very nice summary reports pretty quickly. Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Jenny.S wrote: Hi, Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
Sorry about that, I had thought that after I posted and logged off. Below
is the equation, $A$1 is a cell in the second tab that the month is put into that I want to report. B3:B17 is a list of department, TD is the column that the department is in in the 1st tab, C2:W2 is the injury types, TI is the column that the injury is in in the 1st tab. There are 292 records in the 1st tab. Pivot table gives a great summary, but I'm not too happy with the graph. Need to have a play with that. The result of the formula below is "0", it should be "2" =SUMPRODUCT(--(TD=B3)*(TI=C2)*(M=$A$1)) Sorry, I should have posted my equation last time. Thanks, Jenny.S "Dave Peterson" wrote: Share the addresses of the ranges that contain each of the categories. Share the formula you tried. Jenny.S wrote: Thanks, have had a play with pivot tables. I have named ranges in my spreadsheet, i.e: "M" is the column representing the month, "TD" is department and "TI" is injury type. I am having trouble with getting the =sumproduct to work and to put the results in a different tab. Do you have any suggestions? Thanks, Jenny.S "Dave Peterson" wrote: Maybe... =sumproduct(--(sheet1!a1:a100="accident type 1"), --(text(sheet1!b1:b100,"yyyymm")="200907"), --(sheet1!c1:c100="dept number here")) 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 ============ But you may want to look into a pivottable. If your data is laid out nicely (and it sounds like it is), you could get some very nice summary reports pretty quickly. Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Jenny.S wrote: Hi, Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
You can't use the whole column unless you're using xl2007.
try: =SUMPRODUCT(--(TD1:Td999=B3),--(TI1:Ti999=C2),--(m1:m999=$A$1)) or in xl2007 =SUMPRODUCT(--(TD:Td=B3),--(TI:Ti=C2),--(m:m=$A$1)) Jenny.S wrote: Sorry about that, I had thought that after I posted and logged off. Below is the equation, $A$1 is a cell in the second tab that the month is put into that I want to report. B3:B17 is a list of department, TD is the column that the department is in in the 1st tab, C2:W2 is the injury types, TI is the column that the injury is in in the 1st tab. There are 292 records in the 1st tab. Pivot table gives a great summary, but I'm not too happy with the graph. Need to have a play with that. The result of the formula below is "0", it should be "2" =SUMPRODUCT(--(TD=B3)*(TI=C2)*(M=$A$1)) Sorry, I should have posted my equation last time. Thanks, Jenny.S "Dave Peterson" wrote: Share the addresses of the ranges that contain each of the categories. Share the formula you tried. Jenny.S wrote: Thanks, have had a play with pivot tables. I have named ranges in my spreadsheet, i.e: "M" is the column representing the month, "TD" is department and "TI" is injury type. I am having trouble with getting the =sumproduct to work and to put the results in a different tab. Do you have any suggestions? Thanks, Jenny.S "Dave Peterson" wrote: Maybe... =sumproduct(--(sheet1!a1:a100="accident type 1"), --(text(sheet1!b1:b100,"yyyymm")="200907"), --(sheet1!c1:c100="dept number here")) 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 ============ But you may want to look into a pivottable. If your data is laid out nicely (and it sounds like it is), you could get some very nice summary reports pretty quickly. Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Jenny.S wrote: Hi, Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
Hello,
IMHO Sumproduct is a suboptimal approach here. See here why: http://sulprobil.com/html/sumproduct.html If you do not like to use pivot tables I suggest to use one of my UDF's Pfreq, Mfreq or Pstat: http://sulprobil.com/html/listfreq.html Regards, Bernd |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
Sumproduct can do this. Backgound reading http://www.contextures.com/xlFunctio...tml#SumProduct http://en.allexperts.com/q/Excel-105...-functions.htm Example Count the number of matches in the range A1:C12 that match the criterea in cells A16-C15 =SUMPRODUCT(--(MONTH($B$1:$B$12)=MONTH(B15))*($C$1:$C$12=C15)*($ D$1:$D$12 =D15)) The above assumes: - B15 contains a full date (i.e not "June" or "6" but for example "6/6/2009") and $B$1:$B$12 contains dates - C16 and D16 contain values that match data from the above columns and require no conversion. You many find it easier to handle you date matching if you use a helper column to convert your date to month: =TEXT(B1,"mmm") Where B1 is a date The result becomes Jun For example, if you converted in column E, your formula then becomes =SUMPRODUCT(($C$1:$C$12=C15)*($D$1:$D$12=D15)*($E$ 1:$ED$12=E15)) -- Lil ?B?SmVubnkuUw==?= wrote in : Hi, Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. -- |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
Thanks, please see my reply to Dave. Can you help?
Thanks, Jenny "Liliana" wrote: Sumproduct can do this. Backgound reading http://www.contextures.com/xlFunctio...tml#SumProduct http://en.allexperts.com/q/Excel-105...-functions.htm Example Count the number of matches in the range A1:C12 that match the criterea in cells A16-C15 =SUMPRODUCT(--(MONTH($B$1:$B$12)=MONTH(B15))*($C$1:$C$12=C15)*($ D$1:$D$12 =D15)) The above assumes: - B15 contains a full date (i.e not "June" or "6" but for example "6/6/2009") and $B$1:$B$12 contains dates - C16 and D16 contain values that match data from the above columns and require no conversion. You many find it easier to handle you date matching if you use a helper column to convert your date to month: =TEXT(B1,"mmm") Where B1 is a date The result becomes Jun For example, if you converted in column E, your formula then becomes =SUMPRODUCT(($C$1:$C$12=C15)*($D$1:$D$12=D15)*($E$ 1:$ED$12=E15)) -- Lil ?B?SmVubnkuUw==?= wrote in : Hi, Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. -- |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
Similar to Dave, if you can't work out how to make your formula work,
you need to provide further information. ?B?SmVubnkuUw==?= wrote in : Thanks, please see my reply to Dave. Can you help? Thanks, Jenny "Liliana" wrote: Sumproduct can do this. Backgound reading http://www.contextures.com/xlFunctio...tml#SumProduct http://en.allexperts.com/q/Excel-105...-functions.htm Example Count the number of matches in the range A1:C12 that match the criterea in cells A16-C15 =SUMPRODUCT(--(MONTH($B$1:$B$12)=MONTH(B15))*($C$1:$C$12=C15)*($ D$1: $D $12 =D15)) The above assumes: - B15 contains a full date (i.e not "June" or "6" but for example "6/6/2009") and $B$1:$B$12 contains dates - C16 and D16 contain values that match data from the above columns and require no conversion. You many find it easier to handle you date matching if you use a helper column to convert your date to month: =TEXT(B1,"mmm") Where B1 is a date The result becomes Jun For example, if you converted in column E, your formula then becomes =SUMPRODUCT(($C$1:$C$12=C15)*($D$1:$D$12=D15)*($E$ 1:$ED$12=E15)) -- Lil ?B?SmVubnkuUw==?= wrote in : Hi, Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. -- -- |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
Sorry, please see my reply to Dave
"Liliana" wrote: Similar to Dave, if you can't work out how to make your formula work, you need to provide further information. ?B?SmVubnkuUw==?= wrote in : Thanks, please see my reply to Dave. Can you help? Thanks, Jenny "Liliana" wrote: Sumproduct can do this. Backgound reading http://www.contextures.com/xlFunctio...tml#SumProduct http://en.allexperts.com/q/Excel-105...-functions.htm Example Count the number of matches in the range A1:C12 that match the criterea in cells A16-C15 =SUMPRODUCT(--(MONTH($B$1:$B$12)=MONTH(B15))*($C$1:$C$12=C15)*($ D$1: $D $12 =D15)) The above assumes: - B15 contains a full date (i.e not "June" or "6" but for example "6/6/2009") and $B$1:$B$12 contains dates - C16 and D16 contain values that match data from the above columns and require no conversion. You many find it easier to handle you date matching if you use a helper column to convert your date to month: =TEXT(B1,"mmm") Where B1 is a date The result becomes Jun For example, if you converted in column E, your formula then becomes =SUMPRODUCT(($C$1:$C$12=C15)*($D$1:$D$12=D15)*($E$ 1:$ED$12=E15)) -- Lil ?B?SmVubnkuUw==?= wrote in : Hi, Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. -- -- |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting data that meets 3 different Criteria
Tested and working (for me)
=SUMPRODUCT((MONTH(M)=MONTH($A$1))*(TD=$B$1)*(TI=$ C$1)) You would need to change $B$1 and $C$1 to your relevant cells which should make no difference. In cell A1 and range M you need date values (not months). To change this to allow you to enter (say, 6 for June) instead of a full in cell A1 =SUMPRODUCT((MONTH(m)=$A$1)*(TD=$B$1)*(TI=$C$1)) NOTE M, TD and TI need to be the same size. For example in my test I defined M =Sheet1!$A$2:$A$6 TD =Sheet1!$B$2:$B$6 TI =Sheet1!$C$4:$C$8 All have the same number of rows, If one of the ranges has more or less cells, the result is $N/A -- Lil ?B?SmVubnkuUw==?= wrote in : Sorry, please see my reply to Dave "Liliana" wrote: Similar to Dave, if you can't work out how to make your formula work, you need to provide further information. ?B?SmVubnkuUw==?= wrote in : Thanks, please see my reply to Dave. Can you help? Thanks, Jenny "Liliana" wrote: Sumproduct can do this. Backgound reading http://www.contextures.com/xlFunctio...tml#SumProduct http://en.allexperts.com/q/Excel-105...adsheet-Count- functions.htm Example Count the number of matches in the range A1:C12 that match the criterea in cells A16-C15 =SUMPRODUCT(--(MONTH($B$1:$B$12)=MONTH(B15))*($C$1:$C$12=C15)*($ D $1: $D $12 =D15)) The above assumes: - B15 contains a full date (i.e not "June" or "6" but for example "6/6/2009") and $B$1:$B$12 contains dates - C16 and D16 contain values that match data from the above columns and require no conversion. You many find it easier to handle you date matching if you use a helper column to convert your date to month: =TEXT(B1,"mmm") Where B1 is a date The result becomes Jun For example, if you converted in column E, your formula then becomes =SUMPRODUCT(($C$1:$C$12=C15)*($D$1:$D$12=D15)*($E$ 1:$ED$12=E15)) -- Lil ?B?SmVubnkuUw==?= wrote in : Hi, Would love some help with this challenge. Accident data is being recoded in a spreadsheet and has info recorded for 12 month. In a separate tab I want to count the number of instances by a given month, then by a given department, and then by accident type. Have used three separate "countif" statements and can get the number of accidents for a month, the total number for a give department for 12 months, and the total number by accident type for 12 months. I want to be able to get the total for a month, by department, then accident type. Hope someone can help me, thanks. -- -- -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I count data that meets more than one criteria? | Excel Discussion (Misc queries) | |||
| Counting unique text that meets several criteria | Excel Worksheet Functions | |||
| SUM data if meets criteria | Charts and Charting in Excel | |||
| Show only data that meets a certain criteria | Excel Discussion (Misc queries) | |||
| Averaging data that meets a criteria | Excel Worksheet Functions | |||
Hybrid Mode
